Grouping Rows - Function

[OilEconomist]

Hello and thanks in advance for any assistance provided. I have written a function to group rows, but how can I modify it to avoid the error:

"Run-time error '13': mismatch." on the line :

Set RngGrpLast = RngGrp.Find(What:=GrpValue, After:=RngGrp(i - 1), SearchDirection:=xlPrevious)

within the function:

Function GroupRowsF(ShtName As String, ClmRngLet As String, GrpRowStart As Long, GrpRowEnd As Long) As String

I am attempting to take a specific set of rows which have been grouped as one already:

to get this final output.

I tried to modify the following code which was pretty slick, but it would not work for me: Quick VBA/Macro to group columns based on cell values in a row header

Code is as follows:

Sub TestGroupRowsFunction()

 Dim iLoop As Long
 Dim LR As Long
 Dim GrpRowStart As Long
 Dim GrpRowEnd As Long
 
 Dim ShtName As String
 Dim GroupRows As String
 Dim ClmRngLet As String
 
 
 GrpRowStart = 14
 GrpRowEnd = 20
 ClmRngLet = "C"
 ShtName = ActiveSheet.name

 
 GroupRows = GroupRowsF(ShtName, ClmRngLet, GrpRowStart, GrpRowEnd)
 
 

End Sub



'****************************************************************************************************
'This function groups rows

Function GroupRowsF(ShtName As String, ClmRngLet As String, GrpRowStart As Long, GrpRowEnd As Long) As String

    'Function variable
        'ShtName as String - the Sheet where the grouping needs to take place
        'ClmRngLet As String - the Column Letter of the Range where thr grouping is focused
        'GrpRowStart As Long - starting row of the rows to be grouped
        'GrpRowEnd As Long - end row of the rows to be grouped. If this is 0, it will find the _
            value within this function by use of the last row function - LastRowF


    'Dimensioning
        Dim i As Long
        Dim LR As Long

        Dim FRGrp As Long 'first row of the group, but will not be grouped
        Dim SRGrp As Long 'starting row to group
        Dim LRGrp As Long 'ending row to group

        Dim ShtNameActv As String
        Dim GrpValue As String

        Dim RngGrp As Range
        Dim RngGrpLast As Range



    'Code for the grouping

        'Set the Range
            Set RngGrp = Range(ClmRngLet & GrpRowStart & ":" & ClmRngLet & GrpRowEnd)


        'Group settings
         With Sheets(ShtName).Outline
          .AutomaticStyles = False
          .SummaryRow = xlAbove
          .SummaryColumn = xlLeft
         End With
    
    
        'Loop to perform grouping
         With Sheets(ShtName)
            For i = GrpRowStart To GrpRowEnd
     
                FRGrp = i
                SRGrp = FRGrp + 1
     
                GrpValue = Range(ClmRngLet & i)
                    Set RngGrpLast = RngGrp.Find(What:=GrpValue, After:=RngGrp(i - 1), _
                        SearchDirection:=xlPrevious)
                    LRGrp = RngGrpLast.Row
                    If FRGrp <> LRGrp Then Rows(SRGrp & ":" & LRGrp).Group
                
                SRGrp = LRGrp + 1
                i = LRGrp
    
            Next i
        End With
    
    GroupRowsF = "Done"

End Function

 

[BSALV]

Sub TestGroupRowsFunction()
     Group_Rows ActiveSheet.Name, "C", 14, 20                   'give sheetname, columnletter, 1st and last row
End Sub

Function Group_Rows(ShtName As String, ClmRngLet As String, GrpRowStart As Long, GrpRowEnd As Long)
     Dim sh, i, sp
     Set sh = Sheets(ShtName)                                   'that sheet
     sh.Cells(GrpRowStart, ClmRngLet).Resize(GrpRowEnd - GrpRowStart + 1).Name = "MyGroup"     'make a named range
     sp = Split(GrpRowStart - 1 & "|" & Join(Filter([transpose(if(mygroup<>offset(mygroup,1,,,),row(mygroup),"~"))], "~", 0), "|") & "|" & GrpRowEnd, "|")     'the key-rows (minus1) = next row is the start of a new group
     For i = 0 To UBound(sp) - 1                                'loop through all those rownumbers
          If sp(i + 1) - sp(i) >= 2 Then                        'at least 2 rows difference (you can group 1 row !)
               sh.Cells(sp(i) + 2, 1).Resize(sp(i + 1) - sp(i) - 1).EntireRow.Group     'offset 2 and number of rows to define group
          End If
     Next
End Function

 

[OilEconomist]

Sub TestGroupRowsFunction()
     Group_Rows ActiveSheet.Name, "C", 14, 20                   'give sheetname, columnletter, 1st and last row
End Sub

Function Group_Rows(ShtName As String, ClmRngLet As String, GrpRowStart As Long, GrpRowEnd As Long)
     Dim sh, i, sp
     Set sh = Sheets(ShtName)                                   'that sheet
     sh.Cells(GrpRowStart, ClmRngLet).Resize(GrpRowEnd - GrpRowStart + 1).Name = "MyGroup"     'make a named range
     sp = Split(GrpRowStart - 1 & "|" & Join(Filter([transpose(if(mygroup<>offset(mygroup,1,,,),row(mygroup),"~"))], "~", 0), "|") & "|" & GrpRowEnd, "|")     'the key-rows (minus1) = next row is the start of a new group
     For i = 0 To UBound(sp) - 1                                'loop through all those rownumbers
          If sp(i + 1) - sp(i) >= 2 Then                        'at least 2 rows difference (you can group 1 row !)
               sh.Cells(sp(i) + 2, 1).Resize(sp(i + 1) - sp(i) - 1).EntireRow.Group     'offset 2 and number of rows to define group
          End If
     Next
End Function

Thank you so much @BSALV It worked perfectly.

 

[OilEconomist]

Sub TestGroupRowsFunction()
     Group_Rows ActiveSheet.Name, "C", 14, 20                   'give sheetname, columnletter, 1st and last row
End Sub

Function Group_Rows(ShtName As String, ClmRngLet As String, GrpRowStart As Long, GrpRowEnd As Long)
     Dim sh, i, sp
     Set sh = Sheets(ShtName)                                   'that sheet
     sh.Cells(GrpRowStart, ClmRngLet).Resize(GrpRowEnd - GrpRowStart + 1).Name = "MyGroup"     'make a named range
     sp = Split(GrpRowStart - 1 & "|" & Join(Filter([transpose(if(mygroup<>offset(mygroup,1,,,),row(mygroup),"~"))], "~", 0), "|") & "|" & GrpRowEnd, "|")     'the key-rows (minus1) = next row is the start of a new group
     For i = 0 To UBound(sp) - 1                                'loop through all those rownumbers
          If sp(i + 1) - sp(i) >= 2 Then                        'at least 2 rows difference (you can group 1 row !)
               sh.Cells(sp(i) + 2, 1).Resize(sp(i + 1) - sp(i) - 1).EntireRow.Group     'offset 2 and number of rows to define group
          End If
     Next
End Function

@BSALV once again thanks very much!

A few questions.

When you dimensioned the variables in the following line:

Dim sh, i, sp"

I assumed sh is a worksheet based in the code:

Set sh = Sheets(ShtName)

But how were you able to do that without explicitly making the statement:

 Dim sh as Sheet

Is i a integer/long variable, where the same question. How did you do it without having to declare it?

what is sp?

For the following line of code, based on the code and comments, I assume this was a Name Range, but you did not declare Is there no need to since it was put at the end. Is "MyGroup in fact a name range"

Sht.Cells(GrpRowStart, ClmRngLet).Resize(GrpRowEnd - GrpRowStart + 1).name = "MyGroup"

When I move that to the beginning, it gives me an error "Compile error: Expected: line number or label or statement or end of statement." Is this a named range and versus the conventional Dim

"MyGroup" = Sht.Cells(GrpRowStart, ClmRngLet).Resize(GrpRowEnd - GrpRowStart + 1).name

 

[BSALV]

First, i thought to do it shorter, with less variables, but it wasn't.
But now you give only one variabel to the 2nd macro, the range, that's easier.

Sub Group_MyRange_Rows()
     Group_Rows ActiveSheet.Range("C14:C50")                    'your range
End Sub

Sub Group_Rows(MyColumn As Range)
     Dim iRow0, sp, i

     With MyColumn
          If .Columns.Count > 1 Then MsgBox "your range " & .Address & " has more then 1 column": Exit Sub
          iRow0 = .Row - 1                                      'the rownumber of the row before this range = row of the 1st cell -1
          If iRow0 = 0 Then MsgBox "this macro can't group a range that starts in the first row": Exit Sub
          .Name = "MyGroup"                                     'make a named range
          sp = Split(iRow0 & "|" & Join(Filter([transpose(if(mygroup<>offset(mygroup,1,,,),row(mygroup),"~"))], "~", 0), "|") & "|" & iRow0 + .Rows.Count - 1, "|")     'the key-rows (minus1) = next row is the start of a new group
          For i = 0 To UBound(sp) - 1                           'loop through all those rownumbers
               If sp(i + 1) - sp(i) >= 2 Then                   'at least 2 rows difference (you can group 1 row !)
                    .Cells(sp(i) + 2 - iRow0, 1).Resize(sp(i + 1) - sp(i) - 1).EntireRow.Group     'offset 2 and number of rows to define group
               End If
          Next
     End With
End Sub

Sub UnGroup_1_Level()
     ActiveSheet.Range("C22:C28").Rows.Ungroup                  'if this range is gouped, it takes out the last level
End Sub

Sub Ungroup_Complet()
     ActiveSheet.Range("C14:C50").ClearOutline                  'this takes out all the grouplevels
End Sub

It's no obligation to declare all variables, except if you add "option explicit" at the beginning of the module.

If you don't do so, then VBA takes care itself and starts by declaring them variant and later updates that.
By declaring, you makes it VBA easier, it uses the memory more effective, ... .
But the gain at the level, you and i work, that's peanuts.
So as rookie, i declared everything, now i do it only to find errors easier.
For example, i declare here above the variabel iRow0 but nothing behind, so it becomes a variant. Why ? You see that further the spelling is like in the declaration with a capital letter as 2nd character. If, in a big macro with lots of variables, i find an important variable not correct spelted, then i know i used the wrong name. So i use that declaration now more as spell-controller then as a real tool.

When you debu your program, step by step in the VBA editor, you can add a window to see the "lokale variabelen" (see screenshot, in english perhaps Image>Window local variables).
The next screenshot is that window when the program is at the line " For i = 0 To UBound(sp) - 1 ", so just after the splitting.
In that preceeding "split"-line, VBA does something more complicate, but as general description, it evaluates your range and keeps only the rownumbers of the last cell of a range of identical cells. Suppose C15:C19 all contain "A", then 19 is remembered. If you do so yourself, you'll find other strings depending on the cellvalues in your range.

So i advice you to do that for a moment now and go step-by-step with F8 through the macro. (Start in " Group_MyRange_Rows")
At the beginning of the macro "Group_Rows(MyColumn As Range)", all used variables, even those which aren't declared are declared by VBA and you see that sp is "Variant/Empty" and a few steps later it changes in "Variant/string" and gets a "+" in front, so you can group/ungroup that one. If you do so, you see all the relevant rownumbers which are used in the following "For ... next"-loop

So, you don't have to declare all your variables, if not, VBA does.
In the beginning, as rookie, it's better to do, later you can become more relaxed.
Do you understand ? Did you do the debug succesfully ?