Cells position and grouping them

[streem]

Hi everyone,

I'm just starting up with VBA to get rid of some manual work I have to usually do.

I have to group certain rows in sheets, in this case it would be anything between ranges 1 and 2, 2 and 3, 3 and 4 for each sheet. "Total Revenues Post RMC" and so on are constant for every sheet, so I'm trying to figure out how to do it for one and then just use "For" for the rest of them. I came up with something like this below

Sub Grouping_Rows()

Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim Range4 As Range


For x = 2 To Sheets.Count
    Set Range1 = Range("D:D").Find("Total Revenues Post RMC", After:=Range("D9"), MatchCase:=True)
    Set Range2 = Range("D:D").Find("Sector Direct Costs", After:=Range("D9"), MatchCase:=True)
    Set Range3 = Range("D:D").Find("Country Direct Costs", After:=Range("D9"), MatchCase:=True)
    Set Range4 = Range("D:D").Find("Product Direct Costs", After:=Range("D9"), MatchCase:=True)


ActiveSheet.Range(Range1 + 1, Range2 - 1).Select.Rows.Group


Next


End Sub

I think the Set Range lines don't return range but the value of cell for some reason.. I would appreciate any help

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub Grouping_Rows()

Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim Range4 As Range
Dim x As Long

For x = 2 To Sheets.Count
    Set Range1 = Sheets(x).Range("D:D").Find("Total Revenues Post RMC", After:=Range("D9"), MatchCase:=True)
    Set Range2 = Sheets(x).Range("D:D").Find("Sector Direct Costs", After:=Range("D9"), MatchCase:=True)
    Set Range3 = Sheets(x).Range("D:D").Find("Country Direct Costs", After:=Range("D9"), MatchCase:=True)
    Set Range4 = Sheets(x).Range("D:D").Find("Product Direct Costs", After:=Range("D9"), MatchCase:=True)


Sheets(x).Range(Range1.Offset(1).row & ":" & Range2.Offset(-1).row).Group
Sheets(x).Range(Range3.Offset(1).row & ":" & Range4.Offset(-1).row).Group

Next


End Sub

 

[streem]

Ok, that works great, thanks !

Now I'm wondering if there is any smart way to make sure if one of the cells is not in worksheet, the formula would consider the next one in the list until the last one ?

 

[Fluff]

Maybe

Sub Grouping_Rows()
Dim MyRng(3) As Range
Dim x As Long, i As Long, j As Long

For x = 2 To Sheets.Count
    Set MyRng(0) = Sheets(x).Range("D:D").Find("Total Revenues Post RMC", After:=Range("D9"), MatchCase:=True)
    Set MyRng(1) = Sheets(x).Range("D:D").Find("Sector Direct Costs", After:=Range("D9"), MatchCase:=True)
    Set MyRng(2) = Sheets(x).Range("D:D").Find("Country Direct Costs", After:=Range("D9"), MatchCase:=True)
    Set MyRng(3) = Sheets(x).Range("D:D").Find("Product Direct Costs", After:=Range("D9"), MatchCase:=True)
   
   For i = 0 To 3
      If Not MyRng(i) Is Nothing Then
         For j = i + 1 To 3
            If Not MyRng(j) Is Nothing Then
               Sheets(x).Range(MyRng(i).Offset(1).row & ":" & MyRng(j).Offset(-1).row).Group
               i = i + j
               Exit For
            End If
         Next j
      End If
   Next i
Next
End Sub