ExcelBanter - View Single Post - Modify code for multiple sheets-Help defining array

ToddEZ

Thanks Rick!

"Rick Rothstein (MVP - VB)" wrote:

First off, use the modified code I posted under my original message. Now,
the number 3 through 5 which I am using in my code do not correspond to
sheet names, they are the index numbers of the sheet name tabs with the
left-most one having an index number of 1, the second left-most one having
an index number of 2 and so on... no matter what "names" appear on those
tabs. I structured my code that way because you said "macro to operate
across sheets 3, 4, & 5 rather than just sheet 1". Anyway, there is a minor
problem with the code I initially posted... the "unhide" statement is in the
wrong place. Here is the modified code you should use if you stick with the
tab sheet ordering I just described.

Dim X As Long
For X = 3 To 5
With Worksheets(X).Range("H1:H100")
Worksheets(X).UsedRange.Rows.Hidden = False
Do Until .Find("No") Is Nothing
.Find("No").EntireRow.Delete
Loop
End With
Next

So, if you have listed your names in order, then my code would process them
as long as SOAA is the third tab over from the left, SOC is the fourth tab
over from the left and SAR is the fifth tab over from the left. If that is
how your tabs are positioned, then try out the modified code I posted.

However, if this is not how your tabs are positioned, then you can use this
more generic version of the above...

Dim X As Long
Dim MySheets() As String
MySheets = Split("SOAA,SOC,SAR", ",")
For X = 0 To UBound(MySheets)
Worksheets(MySheets(X)).UsedRange.Rows.Hidden = False
With Worksheets(MySheets(X)).Range("H1:H100")
Do Until .Find("No") Is Nothing
.Find("No").EntireRow.Delete
Loop
End With
Next

where you declare your sheet names inside the Split statement as shown.

Rick

"ToddEZ" wrote in message
...
I guess I should have been clearer in my question. The sheet names
actually
vary (not "sheet 1, sheet 2, etc"). They are SOAA, SOC, SAR, Checklist,
and
a few others. Is there a way I can select these sheets and then run the
marco for each sheet?

Thanks agian.

"Rick Rothstein (MVP - VB)" wrote:

If I understand your question correctly, I think this code will do what
you
want...

Dim X As Long
ActiveSheet.UsedRange.Rows.Hidden = False
For X = 3 To 5
With Worksheets(X).Range("H1:H100")
Do Until .Find("No") Is Nothing
.Find("No").EntireRow.Delete
Loop
Next

Rick

"ToddEZ" wrote in message
...
Hi,

I need some help modifying this "hide row" macro to operate accross
sheets
3, 4, & 5 rather than just sheet 1. I realize that I can just copy the
same
code several times (changing the sheet reference) but I was hoping
there
is
an eaiser way to define a multiple sheet array for this.

Thanks in advance,

What is the Dim cell As Range
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In Worksheets("Sheet1").Range("h1:h100")
If cell.Text = "No" Then cell.EntireRow.Hidden = True
Next cell
End With