Macro to hide rows with zero in specifc column range




I have a workbook with multiple sheets. I would like to put together a
macro that will hide the rows in a given range on all sheets. For example,
on Sheet 1 the range of cells I need to evaluate for zero are C9:63. If any
of the cells in the range are zero, I want to hide the corresponding row. So
if C11 is zero, row 11 is hidden. I would repeat the macro for the same
ranges on Sheet 2, 3, 4,...

Any help is appreciated.




Luke M

Sub HideRows()
For Each sh In ThisWorkbook.Worksheets
For Each c In sh.Range("C9:C63")
If c.Value = 0 Then
c.EntireRow.Hidden = True
c.EntireRow.Hidden = False
End If
Next c
Next sh
End Sub



Gary Brown

how about something like this....
Sub HideZeroRows()
Dim blnGoodSheet As Boolean
Dim iStartRow As Long
Dim iEndRow As Long
Dim rngCell As Range
Dim strColumn As String
Dim wks As Worksheet

'- - V A R I A B L E S - - - - -
iStartRow = 9
iEndRow = 63
strColumn = "C"
'- - - - - - - - - - - - - - - -

For Each wks In Worksheets
blnGoodSheet = False
Select Case wks.Name
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
blnGoodSheet = True
Case Else
blnGoodSheet = False
End Select

If blnGoodSheet = True Then
For Each rngCell In Range(strColumn & iStartRow & ":" & _
strColumn & iEndRow)
If rngCell.Value = 0 Then
rngCell.EntireRow.Hidden = True
End If
Next rngCell
End If
Next wks
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question