hiding or showing rows based on a cell value

[jordanpcpre]

Is there a way to hide or show rows based whether or not there is a specific
value in a specific cell. For example, if D5=0, then hide Row5. If D5 does
not equal 0, then show Row5.

Thanks for the help!

 

[Mike H]

Hi

You need to explain a little more. For example at some stage D5 will
persumably have nothing in it so it becomes hidden. How do you then intend to
put something in it to change that bearing in mind you can't see the cell?
The way you intend to do this with affect the solution.

Mike

 

[David Biddulph]

Auto-filter

 

[jordanpcpre]

The cell has a formula in it that is a LOOKUP foruma. So if the LOOKUP
formula has no value, it places 0 in that cell. I would like to be able to
hide the rows that come up with 0.

Thanks!

 

[Gord Dibben]

Sheet event code...............

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Hide rows with formulas but no data
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(2).SpecialCells(xlCellTypeFormulas)
If cell.Text = "" Or cell.Value = 0 Then cell.EntireRow.Hidden = True
Next cell
End With
End Sub

Straight macro.......................

Sub Hide_Rows_With_Zero()
ActiveSheet.Columns(1).EntireRow.Hidden = False
FindVal = 0
Set b = Range("B:B").Find(What:=FindVal, LookIn:=xlValues)
While Not (b Is Nothing)
b.EntireRow.Hidden = True
Set b = Range("B:B").Find(What:=FindVal, LookAt:=xlWhole)
Wend
End Sub


Gord Dibben MS Excel MVP

 

[jordanpcpre]

Would you please explain how I perform the Macro or Event Code below so that
I can hide rows that have a value of 0.

For example: G179 has a Lookup formula that came up with a value of 0. I
would like to hide row 179 because G179=0. I also want to hide other Rows
that have a value of 0 in column G.

Thanks for the help.

 

[Gord Dibben]

The sheet event code will be pasted into the sheet module.

Right-click on the sheet tab and "View Code". Paste into that module. Alt + q
to return to the Excel window.

The hiding and unhiding of any row with a formula in Column B that returns a
zero will be automatic. Adjust Columns(2) to whatever column you like.

(1) is A, (2) is B etc.

The Hide_Rows_With_Zero macro which has to be run manually is stored in a
general module.

Alt + F11 to open the VBE. CTRL + r to open the Project Explorer.

Right-click on your workbook/project name and Insert>Module.

Paste the macro into that module. Edit the B:B to suit

Alt + 1 to feturn to the Excel window.

You can run the macro by Alt + F8 then select and run or assign the macro to a
button or shortcut key combo.


Gord