Hiding Rows Based On Value

Apr 22, 2014

The attached sheet takes information from another sheet in my work book (I am only providing the sheet I was to work with). I want all rows where the value in F is 0 to hide automatically.

Attached File : Book2.xlsx‎

View 3 Replies


ADVERTISEMENT

Hiding Rows Based On Value?

Mar 27, 2012

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = Sheets("Sheet2").Range("A3") Then 'A3 is the cell where your Yes/no choice is
Select Case Target.Value
Case "Yes": Sheets("Sheet1").Range("A7:A22").EntireRow.Hidden = True
Case "No": Sheets("Sheet1").Rows("A7:A22").EntireRow.Hidden = False
End Select
End If
End Sub

View 5 Replies View Related

Hiding Rows Based On Criteria

Dec 22, 2011

I am stuck with a task.

I need to hide all rows in a worksheet except the rows which contain the word which the user inputs through find (CTR+F).

The input word should be captured in a variable and this should be searched in all rows & hide all other rows in the sheet which does not contain this word.

View 2 Replies View Related

Hiding Rows On One Tab Based On Value Of Cell In Another Tab?

Dec 27, 2012

I have numerous tabs in this file, but all of the tabs all summarize to the first tab called "Annual Record".

So after "Annual Record", tabs follow as "WO1", "WO2", etc...

On the tab called "WO1", I have cell AJ5, which can have 3 status selected (Data Validation List), which is either Inactive, Open, or Completed. If cell AJ5 on "WO1" says "Inactive", then I want a named range on "Annual Record" to be hidden. Right now I have the range named "WorkOrder1". This named range is essentially rows 4-7. So if it's easier to hide rows vs. a named range, then so be it. I also would like it to happen automatically (perhaps what you would refer to as a change event). When cell AJ5 is change to either "Open" or "Completed", then the rows would unhide. I'm thinking that this would be repeated for tab "WO2". If cell AJ5 says "Inactive", then rows 8-11 on the tab "Annual Record" or the named range "WorkOrder2" would be hidden. And again, if the cell AJ5 is change to anything other than "Inactive", then the rows or range would unhide.

View 9 Replies View Related

Hiding Rows Based On Condition

Feb 11, 2013

I have a cell with today's date on it. i.e., cell A1 = today()

Then, I have rows of data with one of the columns with a date on it. I've put in an object (button), where when the user presses this button, I want all the rows of data that do not have the date on cell A1 to be hidden.

Then on the second button, when the user presses the button, to unhide all the rows that were previously hidden. To make it easier, I can simply state it to unhide all the previously hidden rows (but NOT columns - there are still hidden columns which I want it to stay hidden).

So in sum, using table below. If I have on cell A1 - today's date is 2 Mar 2013, pressing first button would hide the row with Jane Y's record. Then pressing second button would then unhide all previously hidden rows.

DATE
NAME
DEPT

1 Mar 2013
John X
Accounting

2 Mar 2013
Jane Y
Operations

3 Mar 2013
Joe Z
Marketing

How can I accomplish this?

View 6 Replies View Related

Hiding Rows Based On Value In Cell

Mar 5, 2009

I need to hide 8 rows (30-37) based on a value in cell B28.

If the value is 2 then only show rows 30 and 31
If the value is 3 then only show rows 30, 31 and 32
Then continue this up to the user puts in the value of 8 and no rows are hidden.

View 9 Replies View Related

Hiding Rows Based Upon A Cell Value

Sep 7, 2009

I'm trying to come up with some code that will hide a number of rows within the range row 7 to row 15 based upon the value of cell G1.

e.g if G1 is 1 rows 8-15 are hidden, if it is 2, rows 9-15 are hidden i.e the number of rows visible within that range equals the value of G1.

View 9 Replies View Related

Hiding Rows Based On Multiple Values

Aug 7, 2012

I've tried several approaches on how to code this, but can't figure out how to work it. I have a large spreadsheet; which dynamically changes in both column and row counts. So, I have an array of values. If any cell has a negative value, that cell text needs to be changed to red AND that row needs to remain displayed. If ALL values in that row are >=0 then the row can be hidden.

The array is actually a pivot table named QTD

For Example: If cell D5 = 5 and F5 = -3 then Row 5 should remain displayed.

If Cell D6 = 5 and F6=0 then row 6 should be hidden because all values are > 0

Then I was thinking of using the case statement to hide the rows but can't figure the syntax. I'm open to any other means of performing the taks as long as the initial array dynamically to encompass all the data.

View 7 Replies View Related

Hiding Rows Based On Cell Value Not Working

Jun 26, 2014

I have a macro that I am trying to add a feature to at the end of the code that hides blank rows. I have tried numerous methods based upon other forums, but my code does not seem to work and does not hide blank rows. In red is the section of code that I am having trouble with.

Sub Update()

Dim c As Object
Dim rngA As Range
Dim cc As Object
Dim AA As Range

'Check every cell in the range for matching criteria.

[Code] ...........

View 3 Replies View Related

Hiding Rows Based On Text Value In Column

Feb 2, 2010

I would like to have a VBA macro that would look over my entire spreadsheet and hide the individual row if any cell in Column A contains the text "xyzzy". The "xyzzy" can be anywhere in the cell....beginning, middle or end.

View 9 Replies View Related

Excel 2010 :: Hiding Rows Based On Dropdown

Mar 6, 2013

I am currently working on a workbook for work. It is a basic input output sheet. I have data from work that i copy and paste into sheet 1 and I have it arranged into sheet 2.

I am using windows 7 with Excel 2010, though needs to be compatible with 2007 using XP.

What I am trying to implement. I would have a drop down box in cell AD-4 with the current list

Select
Week 1
Week 2
Week 3
Week 4

What I want to implement is a code that if I select "week 1" from drop down then it will hide the entire row if data in column B = week 2, week 3, week 4, week 6

also

If week 2 is selected then hide entire row if column b = week 3 , week 4, week 5

so basically hide the weeks that are after the current selected week.

In addition to this I would also like to hide the entire row, regardless of drop down selection if data from J, K and O all = 0 or (blank).

(the first row is the current Row labels, I have a lot of current hidden columns).

A
B
J
K
O

Supervisor Name
Agent Name
Needs Complete
Completed w/o Turn in
Needs Turn In

[Code] ........

View 7 Replies View Related

Hiding / Unhiding Rows Based On Cell Input

Jul 2, 2009

I am trying to work out a macro to hide and unhide rows in worksheet 6 based on a cell input (cell I6) in worksheet 1 based on the following:

>> If I6 in Worksheet 1 = 1 or 5 or 6 then Hide Rows 19 to 24 in Worksheet 6
>> If I6 in Worksheet 1 = 2 then Hide Rows 21 to 24 in Worksheet 6
>> If I6 in Worksheet 1 = 3 or 4 or 7 then Hide Rows 17 to 20 in Worksheet 6
>> If I6 in Worksheet 1 = 8 or 9 then Hide Rows 17 to 20 and Rows 23 to 24 in Worksheet 6

View 9 Replies View Related

Hiding Rows In VLOOKUP Sheet Based On Null Values

Dec 30, 2013

I have created a spreadsheet from a master using vlookup. I want to be able to hide rows which contain a null value (NA) in column B (sample attached). Is there a formula I can use which won't interfere with the vlookup or do I need to hide the rows individually? I tried filtering but that only filtered the cell and not the whole row.

test sheet.xlsx

View 3 Replies View Related

Excel 2003 :: Hiding Rows Based On Conditions On Two Worksheets?

Jan 4, 2013

I have two worksheets (Sheet1 and Sheet2). Based on meeting a condition on Sheet1 AND Sheet2, I'd like to use macros to automatically hide rows on Sheet2.

Sheet1 sample:
Type
Selection

[Code]...

The conditions are: If on Sheet1, a Type has the Selection "Include", then any rows on Sheet2 which have that same Type are shown (ie. not hidden).

If on Sheet1, a Type has the Selection "Exclude",then any rows on Sheet2 which have that same Type are hidden.

So in the example tables above, if ONLY Type A = Include, then on Sheet2, the rows for Sample URL1 and Sample URL2 are shown, and the others are hidden.

Also in the example tables above, if Type A = Include AND Type B = Include (and all other Types are Exclude), then on Sheet2, the rows for Sample URL1, Sample URL2 AND Sample URL3 are shown, and the others are hidden.

I'm fine with the concept of If... And... on the same worksheet, it's trying to get it to look at two separate worksheets I'm struggling with!!

View 3 Replies View Related

Excel 2003 :: VBA / Hiding Rows Based On Three Conditions On Two Different Worksheets

Jan 14, 2013

I have a workbook with two worksheets, Sheet1 and Sheet 2. Based on the data entered in the 2nd column on Sheet1, I'd like a macro which hides rows for Events which do not meet the criteria entered on Sheet1.

So in the example below, on Sheet2, I only want to see rows where the Country=Scotland, AND the Location=Glasgow, AND the Start Date is greater than (or equal) the Start Date on Sheet1.

Sample of Sheet1
Country
Scotland

Location
Glasgow

Start Date (dd/mm/yyyy)
30/04/2013

[code]....

So in this scenario, only the FIRST row meets the 3 criteria (Scotland, Glasgow, date after 30th April), thus all the other rows should be hidden.

View 6 Replies View Related

Hiding And Unhiding Rows In Multiple Sheets Based On Specific Cell Value With VBA

Jan 4, 2013

I am trying to find code that will allow me to hide a set number of rows based on the value of a specific cell which I need to work for two worksheets in the same workbook. Is that even possible?

For example: when i enter 5 into cell D1, I need five rows to be visible on both sheets.

View 9 Replies View Related

Code For Deleting Rows Verses Hiding Rows

May 25, 2013

The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.

Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True
Next cell

Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete
Next cell

View 4 Replies View Related

Hiding Rows...

Jul 1, 2009

I have two command buttons, and need one macro that would hide all blank rows between columns "E" and "O" and from row 9 to 42, and then another macro that would unhide all these rows again.

View 2 Replies View Related

VBA For Hiding Rows?

Oct 29, 2012

I am building a real estate model. What I am trying to do is the following:

If I enter the number of tenants in a cell (let's say the number of tenants is 6 and the cell I enter this number in is cell J11), I would like 6 of 15 rows to be shown below (rows 46-60). Therefore, the 9 unused rows (rows 52-60) would be hidden from sight. Furthermore, if I entered the number 3 for the tenant count, excel would hide 12 rows (rows 49-60). I am sure that this is a VBA code.

View 1 Replies View Related

Hiding Rows With A Cell Containing 0

Oct 25, 2006

a bit of code so that when I open a sheet (which takes data from another sheet) it hides rows which contain a zero in a certain cell?

View 10 Replies View Related

Hiding/Unhiding Rows

Feb 8, 2010

I have a problem with hiding and unhiding columns. Is there a way wherein I can automatically hide the row(s) if the cell = "N/A" and will unhide if it's not equal to "NA". see the attachment Im using excel 2003 btw. You can see in Sheet2 to that it contains the table for the student grades. "N/A" means that they are not enrolled on that subject. While in sheet1, What I want to happen is that you will select a student name and below, it will automatically display the grades of the student on their enrolled subjects only. So, those fields with "N/A" will hide and when I update the value of N/A it will unhide.

View 5 Replies View Related

Hiding Blank Rows

Feb 1, 2010

I have had great success using this code to hide and unhide unused space, but for some reason this morning it broke. Most likely it is my error so I am looking for another solution to resolve the issue. What i would like to be able to do it hide an entire row based on a specific cell being blank. maybe that is what this code is supposed to do but like i said it broke this morning. the cell in ref in the code is unchanged, but i did add formulas to some other cells in the same row. this is why i am looking for a way to do the same thing but based on a specific cell being blank vs what is occuring now.

View 4 Replies View Related

Error 424 When Hiding Rows

Feb 2, 2010

I have some vba code that is used to hide any rows with the cell value of "Finished". The code is below. When I run this code I get a run time error 424 - Object Required. Can you please explain or help as to why I am getting this?

As a side point the data is loaded from an oracle database rather than from the sheet but every other function/code works fine.

View 12 Replies View Related

VBA Hiding Rows With Asterisks

Jan 1, 2012

I have a worksheet with 4000 rows and only 4 columns.

In quite a lot of the rows in column 2 there are asterisks (**'s).

I have to print out the report without these rows.

First i would like to hide the rows and print the report

Second i then need to delete the rows if there is no data in column 4

View 4 Replies View Related

Hiding Only Specific Rows Using VBA

Jun 15, 2012

I have a spreadsheet where I have a simple formula. The formula is IF($A2=$B$2,1,0), and then I drag down to the bottom of the spreadsheet, so whenever it matches it shows a 1.

Now, what I need to do using VBA is hide all the rows that do not have 1's on them. I know how to start the VBA, but not how to finish

Code:
Dim LR as Long
LR = Range("A2").End(xlDown).Row

Because that will allow me to only select the row field that I want to do the Macro on. So, how do I go about hiding rows that only have 0's in them.

View 2 Replies View Related

Hiding Rows Through A Macro

Dec 4, 2006

i'm having trouble hiding rows through a macro. In this case, i'd rather use a macro than use autofilter....

i would like a macro that does the followings...

1. for each row in worksheet (up until row 2000), if the value in the first cell = YES, then hide row, otherwise, leave row as is...

View 9 Replies View Related

Automatically Hiding Rows

Jan 11, 2007

in worksheet "sheet1" I have my inputs... in "sheet2" I have my outputs... my inputs could be anything from 1 to 1000 therefore my outputs would be 1000... on the output sheet, below the last line, i have a whole bunch of text and calcs that I will always need at the bottom... what i would like to happen is if I put in "sheet1" b2 a number, say 5, then "sheet 2" would hide rows 10 to 1005 or if i put in "sheet2" b2 say 925, it hides 930 to 1005...

I created a macro, but it doesn't work at all... and i want it to work automatically without me pushing run macro...

View 9 Replies View Related

Hiding Rows When Blank

Sep 25, 2007

how to hide rows that are blank as a result of formulas in that row returning "".

I have rows between A10 and A80 that I need to test for blank as above an automatically hide them if they are "". I need to redo this each time I view the sheet

Be gentle, I am just a VBA newbie and havent quite finished reading Bill Jelens excellent VBA book purchased on this site.

View 9 Replies View Related

VBA For Hiding/unhiding Rows

Sep 27, 2007

For data validation lists B27 to B30, if any of them is equal to "EBS via ULL" then unhide rows 31 to 42.
If any of the data validation list not equal to "EBS via ULL" then unhide row 44 and hide rows 31 to 42.
If "EBS via ULL" and any other product in the lists is selected, unhide rows 31 to 44.
If validation lists B27 to B30 are all equal to "Select Product..", then hide rows 31 to 44.
I would like to use the worksheets_change event.

View 9 Replies View Related

Hiding Sheets And Rows

Aug 8, 2008

Why this doesn't error but yet it doesn't work either.

Sheets("2 plans, 3 plans, 4 plans").Visible = False

Sheets("1 plan").Range("a26:a416").EntireRow.Hidden = True

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved