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

[Punsterr]

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 Ogilvy]

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

 

[NickHK]

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]

Tom and Nick,

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

Rick