Conditionally Hide Rows
 

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.

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"tamato43" wrote in message ...
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.

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

On Tue, 10 May 2005 13:48:12 -0700, "tamato43"
wrote:

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.

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

"Gord Dibben" wrote:

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

On Tue, 10 May 2005 13:48:12 -0700, "tamato43"
wrote:

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.

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


On Wed, 11 May 2005 06:02:06 -0700, "tamato43"
wrote:

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

"Gord Dibben" wrote:

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

On Tue, 10 May 2005 13:48:12 -0700, "tamato43"
wrote:

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.