Macro to hide rows with zero in specifc column range


D

David

Hello

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.

Thanks
 
Ad

Advertisements

L

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
Else
c.EntireRow.Hidden = False
End If
Next c
Next sh
End Sub
 
Ad

Advertisements

G

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

Top