Modify code for multiple sheets-Help defining array

[Guest]

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

 

[JE McGimpsey]

One way:

Dim rCell As Range
Dim i As Long
For i = 1 To 5
If i <> 2 Then
With Worksheets(i)
.Rows.Hidden = False
For Each rCell In .Range("H1:H100")
With rCell
.EntireRow.Hidden = (.Text = "No")
End With
Next rCell
End With
End If
Next i


Alternatively:

Dim ws As Worksheet
Dim rCell As Range

For Each ws In Worksheets(Array( _
"Sheet1", "Sheet3", "Sheet4", "Sheet5"))
ws.Rows.Hidden = False
For Each rCell In ws.Range("H1:H100")
With rCell
.EntireRow.Hidden = (.Text = "No")
End With
Next rCell
Next ws

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

Whoops! I left out the End With statement...

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
End With
Next

Rick

 

[Guest]

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\)]

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

 

[Guest]

Thanks Rick!

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