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.
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