| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify code for multiple sheets-Help defining array
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify code for multiple sheets-Help defining array
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 In article , ToddEZ wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify code for multiple sheets-Help defining array
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify code for multiple sheets-Help defining array
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 "Rick Rothstein (MVP - VB)" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify code for multiple sheets-Help defining array
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify code for multiple sheets-Help defining array
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify code for multiple sheets-Help defining array
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Modify macro code to export multiple cell contents to multiple Text Files | Excel Programming | |||
| Defining a function to use multiple places in code | Excel Programming | |||
| Array, multiple sheets | Excel Programming | |||
| Printing multiple sheets in an array | Excel Programming | |||
| selecting multiple sheets by use of an array? | Excel Programming | |||
Linear Mode
