Prev: making a correction in a row of excel?
Next: letterhead in excel
From: David on 20 Apr 2010 14:40
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
From: Luke M on 20 Apr 2010 15:10
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

--
Best Regards,

Luke M
"David" <David(a)discussions.microsoft.com> wrote in message
news:FB353EF5-356A-4FFF-A293-7907A3E6939F(a)microsoft.com...
> 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


From: Gary Brown on 20 Apr 2010 16:04
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
'/----------------------------------------------------------------/
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"David" wrote:

> 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
 | 
Pages: 1
Prev: making a correction in a row of excel?
Next: letterhead in excel