Conditionally Hide Rows

[Guest]

Hello everyone,

This is my second posting.

Does anyone know a VBA script to conditionally hide selected rows if they
equal zero or are left blank?

Please help.

 

[David McRitchie]

Use a formula on the worksheet to provide a null value or an "X" and then
filter on that column. Data, Filter

You could use advanced filter and include the formula.

More information on Filters:
http://www.contextures.com/tiptech.html

 

[Gord Dibben]

Entire rows are 0 or blank?

Sub DeleteEmptyRows()
Dim LastRow As Long
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

Just rows with a certain column's cells blank or zero?

Sub HideBlank_Zeros_Rows()
'using set column
Dim RngCol As Range
Dim i As Range
Set RngCol = Range("B1", Range("B" & Rows.Count). _
End(xlUp).Address)
For Each i In RngCol
If i.Value = "" Or i.Value = "0" Then _
i.EntireRow.Hidden = True
Next i
End Sub


Gord Dibben Excel MVP

 

[Guest]

Thanks Gord this helps, but is there a way to hide instead of deleting?

 

[Gord Dibben]

Sorry about that tam!

Change the .delete line to entirerow.hidden = true

Sub DeleteEmptyRows()
Dim LastRow As Long
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then _
Rows(r).EntireRow.Hidden = True
Next r
End Sub


Gord