"With Sheets" Issue - macro on one sheet to affect hidden rows on other sheets

Hi all,

I have a macro assigned to a "Hide Unused Rows Throughout Workbook"
button that is located on the last worksheet in a workbook. I am
writing individual subs to hide specific rows on each worksheet in the
workbook. Below is a sample of one of those subs.

I'm trying to write this as efficiently as possible. I previously had
this set up using a sheets("sheetname").activate at the beginning of
each subroutine. This worked fine, but even with the
Application.Screenupdating set to False, the users could see the macro
cycling through all of the various worksheets in the workbook as it hid
the particular rows called for by the specific macro for each
worksheet. Plus, it seemed to take longer than I would have liked.

I thought I'd try the "With" function for each worksheet's "hide"
macro, but that doesn't seem to work well if it's being called from
another worksheet. Any thoughts?


Sub HideUnusedRowsDiag()

Application.ScreenUpdating = False
With Sheets("Diag")
If (Left(Cells(34, 1).Text, 12)) = "(Enter notes" Then
Rows("34").EntireRow.Hidden = True
Else
Rows("34").EntireRow.Hidden = False
End If
End With
Application.ScreenUpdating = True
End Sub

Put a period in front of rows so it is qualified by the With Object

Sub HideUnusedRowsDiag()

Application.ScreenUpdating = False
With Sheets("Diag")
If (Left(Cells(34, 1).Text, 12)) = "(Enter notes" Then
.Rows("34").EntireRow.Hidden = True
Else
.Rows("34").EntireRow.Hidden = False
End If
End With
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"Punsterr" <punsterr@hotmail.com> wrote in message
news:1140490159.274071.194160@f14g2000cwb.googlegroups.com...
> Hi all,
>
> I have a macro assigned to a "Hide Unused Rows Throughout Workbook"
> button that is located on the last worksheet in a workbook. I am
> writing individual subs to hide specific rows on each worksheet in the
> workbook. Below is a sample of one of those subs.
>
> I'm trying to write this as efficiently as possible. I previously had
> this set up using a sheets("sheetname").activate at the beginning of
> each subroutine. This worked fine, but even with the
> Application.Screenupdating set to False, the users could see the macro
> cycling through all of the various worksheets in the workbook as it hid
> the particular rows called for by the specific macro for each
> worksheet. Plus, it seemed to take longer than I would have liked.
>
> I thought I'd try the "With" function for each worksheet's "hide"
> macro, but that doesn't seem to work well if it's being called from
> another worksheet. Any thoughts?
>
>
> Sub HideUnusedRowsDiag()
>
> Application.ScreenUpdating = False
> With Sheets("Diag")
> If (Left(Cells(34, 1).Text, 12)) = "(Enter notes" Then
> Rows("34").EntireRow.Hidden = True
> Else
> Rows("34").EntireRow.Hidden = False
> End If
> End With
> Application.ScreenUpdating = True
> End Sub
>

Punsterr,
I think you'll find it the omission of "." before the Cells and Row.
Without that qualification, they refer to the sheet that is running the
code, whereas with it they obviously refer back to the object stated in the
With.

NickHK

"Punsterr" <punsterr@hotmail.com> wrote in message
news:1140490159.274071.194160@f14g2000cwb.googlegroups.com...
> Hi all,
>
> I have a macro assigned to a "Hide Unused Rows Throughout Workbook"
> button that is located on the last worksheet in a workbook. I am
> writing individual subs to hide specific rows on each worksheet in the
> workbook. Below is a sample of one of those subs.
>
> I'm trying to write this as efficiently as possible. I previously had
> this set up using a sheets("sheetname").activate at the beginning of
> each subroutine. This worked fine, but even with the
> Application.Screenupdating set to False, the users could see the macro
> cycling through all of the various worksheets in the workbook as it hid
> the particular rows called for by the specific macro for each
> worksheet. Plus, it seemed to take longer than I would have liked.
>
> I thought I'd try the "With" function for each worksheet's "hide"
> macro, but that doesn't seem to work well if it's being called from
> another worksheet. Any thoughts?
>
>
> Sub HideUnusedRowsDiag()
>
> Application.ScreenUpdating = False
> With Sheets("Diag")
> If (Left(Cells(34, 1).Text, 12)) = "(Enter notes" Then
> Rows("34").EntireRow.Hidden = True
> Else
> Rows("34").EntireRow.Hidden = False
> End If
> End With
> Application.ScreenUpdating = True
> End Sub
>

Tom and Nick,

Thanks so much for your quick responses (and for educating me). That
fixed the problem.

Rick