Running a macro in multiple worksheets 1

[rmelnyck]

I have a function that I would like to run in mulitple worksheets. The function hides specific rows if they do not meet certain criteria. I can get it to work in one sheet but when I try to run it in multiple sheets using a loop it seems the value in lastcell does not get updated.

Any help would be appreciated. Thanks

Application.ScreenUpdating = False
On Error Resume Next
Dim ir As Long, mrows As Long, lastcell As Range

Set lastcell = Cells.SpecialCells(xlLastCell)
mrows = lastcell.Row
MsgBox ActiveWorkbook.Worksheets(I).Name & mrows
'Note rows are hidden from the bottom going up
For ir = mrows To 1 Step -1
If Range("a" & ir).Value = UCase(Range("a" & ir).Value) And Left(Range("a" & ir).Value, 1) = " " And Len(Trim(Range("d" & ir).Value)) = 0 And Len(Trim(Range("f" & ir).Value)) = 0 And Len(Trim(Range("h" & ir).Value)) = 0 And Len(Trim(Range("j" & ir).Value)) = 0 And Len(Trim(Range("l" & ir).Value)) = 0 And Len(Trim(Range("n" & ir).Value)) = 0 Then
Rows(ir).EntireRow.Hidden = True
End If
Next
Application.ScreenUpdating = True
End Sub

 

[dbsquared]

use sheets("&quot.select or what ever the name of the sheets are inside your for loop.

example:
for x=1 to 30
sheets(sheet" & x).select
For ir = mrows To 1 Step -1
If Range("a" & ir).Value = UCase(Range("a" & ir).Value) And Left(Range("a" & ir).Value, 1) = " " And Len(Trim(Range("d" & ir).Value)) = 0 And Len(Trim(Range("f" & ir).Value)) = 0 And Len(Trim(Range("h" & ir).Value)) = 0 And Len(Trim(Range("j" & ir).Value)) = 0 And Len(Trim(Range("l" & ir).Value)) = 0 And Len(Trim(Range("n" & ir).Value))= 0 Then
Rows(ir).EntireRow.Hidden = True
End If
Next
next
that will take you through your sheets

To go where no programmer has gone before.

 

[rmelnyck]

Greatm thanks!

 

[rmelnyck]

Your solution works perfectly. The only thing is that my worksheets are named and are not named Sheet1, Sheet2 etc.......

Thanks

 

[rmelnyck]

Nevermind, I got it, I used an array.

Thanks for your help.