Grouping disappears after grouping other column excel - can't use summary row

R1351

New Member
Joined
Sep 15, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I am trying to group columns in Excel. As seen in the screenshots shown below, I have grouped C27 to C30 in the first screenshot. However, when I go to column E and try to group E31 to E35, it does not work and the previous grouping disappears. How can I fix this? I don't want to add a summary row as there is nothing to summarize.

The function of this code is to group white spaces every other column such as row 27 to 30 in column C and rows 31 to 25 in column E.
Cell C26 is the name of a supervisor. Everyone under him is from C26 to C30. That is why I am trying to group the white space.


VBA Code:
    For iColumn = 1 To 11 Step 2
        'loop through rows
        
        
        iStartRow = -999
        
        'from first cell in column
    
        For iRow = wksSupervisor.Cells(1, iColumn).End(xlDown).Row + 1 To wksSupervisor.Cells.SpecialCells(xlCellTypeLastCell).Row
        
            iLastRow = wksSupervisor.Cells(10000, iColumn + 2).End(xlUp).Row
            
            'Check if empty cell has text above
            If IsEmpty(wksSupervisor.Cells(iRow, iColumn)) And Not IsEmpty(wksSupervisor.Cells(iRow - 1, iColumn)) Then
                
                'start row of grouping is current row
                iStartRow = iRow
            
            End If
            
            'check if empty cell has text below
            If IsEmpty(wksSupervisor.Cells(iRow, iColumn)) And Not IsEmpty(wksSupervisor.Cells(iRow + 1, iColumn)) Then
                
                'empty cell
                bFoundLastRow = True
                iEndRow = iRow
                
            End If
            
            
            'Check if we have both the start row to group and last row to group
            If (bFoundLastRow = True Or iRow = iLastRow) And (iStartRow <> iEndRow) And (iStartRow <> -999) Then
                iEndRow = iRow
                If IsEmpty(wksSupervisor.Cells(iEndRow, iColumn)) Then
                    wksSupervisor.Rows(iStartRow & ":" & iEndRow).Group
                    bFoundLastRow = False
                End If
                
            End If
            
            'Create a separate condition grouping 1 row (syntax is different)
            If (bFoundLastRow = True Or iRow = iLastRow) And (iStartRow = iEndRow) Then
                    wksSupervisor.Cells(iRow, iColumn).Select
                    Selection.Rows.Group
                    bFoundLastRow = False
                
            End If
            
        Next iRow
    
    Next iColumn
 

Attachments

  • before grouping.png
    before grouping.png
    49.3 KB · Views: 11
  • Grouping dissapears.png
    Grouping dissapears.png
    82 KB · Views: 10

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,215,028
Messages
6,122,749
Members
449,094
Latest member
dsharae57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top