Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can you hide rows with a formula? | Excel Discussion (Misc queries) | |||
Hide Rows | Excel Discussion (Misc queries) | |||
how to hide rows in a protected sheet | Excel Worksheet Functions | |||
Checkbox to hide and unhide rows Please. | Excel Worksheet Functions | |||
Excel - if cells = 0, how to conditionally hide rows in chart | Excel Worksheet Functions |