How can I find the number of Service Groups to put in this array? Each sheet
has a different number. The next sheet has 200+ service groups. This type
field is in column "H". There is a separator blank row for subtotals between
each service group. The macro works perfect.

thanks,

myTypes = Array("SG01", "SG02", "SG03", "SG04", _
"SG05", "SG06", "SG07", "SG08", "SG09", "SG10", "SG11", _
"SG12", "SG13", "SG14", "SG15", "SG16", "SG17", "SG18", _
"SG19", "SG20", "SG21", "SG22", "SG23", _
"SG24", "SG25", "SG26", "SG27", "SG28", "SG29", _
"SG30", "SG31", "SG32", "SG33", "SG34", "SG35", _
"SG36", "SG37", "SG38", "SG39", "SG40", "SG41", _
"SG42", "SG43", "SG44", "SG45", "SG46", "SG47", _
"SG48", "SG49", "SG50", "SG51", "SG52", "SG53", _
"SG54", "SG55", "SG56", "SG57", "SG58", "SG59", _
"SG60", "SG61", "SG62", "SG63", "SG64", "SG65", _
"SG66", "SG67", "SG68", "SG69", "SG70", "SG71", _
"SG72", "SG73", "SG74", "SG75", "SG76", "SG77", _
"SG78", "SG79", "SG80", "SG81", "SG82", "SG83", _
"SG84")

---------macro-------------

Sub testme01()

Dim myTypes As Variant
Dim iCtr As Long
Dim wks As Worksheet
Dim FoundCell As Range

myTypes = Array("SG01", "SG02", "SG03", "SG04", _
"SG05", "SG06", "SG07", "SG08", "SG09", "SG10", "SG11", _
"SG12", "SG13", "SG14", "SG15", "SG16", "SG17", "SG18", _
"SG19", "SG20", "SG21", "SG22", "SG23", _
"SG24", "SG25", "SG26", "SG27", "SG28", "SG29", _
"SG30", "SG31", "SG32", "SG33", "SG34", "SG35", _
"SG36", "SG37", "SG38", "SG39", "SG40", "SG41", _
"SG42", "SG43", "SG44", "SG45", "SG46", "SG47", _
"SG48", "SG49", "SG50", "SG51", "SG52", "SG53", _
"SG54", "SG55", "SG56", "SG57", "SG58", "SG59", _
"SG60", "SG61", "SG62", "SG63", "SG64", "SG65", _
"SG66", "SG67", "SG68", "SG69", "SG70", "SG71", _
"SG72", "SG73", "SG74", "SG75", "SG76", "SG77", _
"SG78", "SG79", "SG80", "SG81", "SG82", "SG83", _
"SG84")

Set wks = Worksheets("VOD")

With wks.Range("H1").EntireColumn
For iCtr = LBound(myTypes) To UBound(myTypes)
Set FoundCell = .Cells.Find(what:=myTypes(iCtr), _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
Searchdirection:=xlPrevious, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox myTypes(iCtr) & " wasn't found!"
Else
FoundCell.Offset(1, 0).Resize(23).EntireRow.Insert
End If
Next iCtr
End With

End Sub

Sorry I put this one in the wrong group.

"Janis" wrote:

How can I find the number of Service Groups to put in this array? Each sheet
has a different number. The next sheet has 200+ service groups. This type
field is in column "H". There is a separator blank row for subtotals between
each service group. The macro works perfect.

thanks,

myTypes = Array("SG01", "SG02", "SG03", "SG04", _
"SG05", "SG06", "SG07", "SG08", "SG09", "SG10", "SG11", _
"SG12", "SG13", "SG14", "SG15", "SG16", "SG17", "SG18", _
"SG19", "SG20", "SG21", "SG22", "SG23", _
"SG24", "SG25", "SG26", "SG27", "SG28", "SG29", _
"SG30", "SG31", "SG32", "SG33", "SG34", "SG35", _
"SG36", "SG37", "SG38", "SG39", "SG40", "SG41", _
"SG42", "SG43", "SG44", "SG45", "SG46", "SG47", _
"SG48", "SG49", "SG50", "SG51", "SG52", "SG53", _
"SG54", "SG55", "SG56", "SG57", "SG58", "SG59", _
"SG60", "SG61", "SG62", "SG63", "SG64", "SG65", _
"SG66", "SG67", "SG68", "SG69", "SG70", "SG71", _
"SG72", "SG73", "SG74", "SG75", "SG76", "SG77", _
"SG78", "SG79", "SG80", "SG81", "SG82", "SG83", _
"SG84")

---------macro-------------

Sub testme01()

Dim myTypes As Variant
Dim iCtr As Long
Dim wks As Worksheet
Dim FoundCell As Range

myTypes = Array("SG01", "SG02", "SG03", "SG04", _
"SG05", "SG06", "SG07", "SG08", "SG09", "SG10", "SG11", _
"SG12", "SG13", "SG14", "SG15", "SG16", "SG17", "SG18", _
"SG19", "SG20", "SG21", "SG22", "SG23", _
"SG24", "SG25", "SG26", "SG27", "SG28", "SG29", _
"SG30", "SG31", "SG32", "SG33", "SG34", "SG35", _
"SG36", "SG37", "SG38", "SG39", "SG40", "SG41", _
"SG42", "SG43", "SG44", "SG45", "SG46", "SG47", _
"SG48", "SG49", "SG50", "SG51", "SG52", "SG53", _
"SG54", "SG55", "SG56", "SG57", "SG58", "SG59", _
"SG60", "SG61", "SG62", "SG63", "SG64", "SG65", _
"SG66", "SG67", "SG68", "SG69", "SG70", "SG71", _
"SG72", "SG73", "SG74", "SG75", "SG76", "SG77", _
"SG78", "SG79", "SG80", "SG81", "SG82", "SG83", _
"SG84")

Set wks = Worksheets("VOD")

With wks.Range("H1").EntireColumn
For iCtr = LBound(myTypes) To UBound(myTypes)
Set FoundCell = .Cells.Find(what:=myTypes(iCtr), _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
Searchdirection:=xlPrevious, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox myTypes(iCtr) & " wasn't found!"
Else
FoundCell.Offset(1, 0).Resize(23).EntireRow.Insert
End If
Next iCtr
End With

End Sub