VBA Grouping Macro needing to be modified

[jpblev]

I found a macro that almost gets me what I need and was wondering if someone would help to modify. The macro I have is grouping column A and leaving the first row of each HCode ungrouped. What I need is to group Column A leaving the last (Total) row of each HCode ungrouped:

-- corrupted image removed --

Here's the Macro that needs to be adjust:

Sub GroupRows()
Dim myValue As Variant
Dim myCol As String
Dim myRow As Long
Dim startRow As Long
Dim lastRow As Long
'Which column to look at?
myCol = "A"
'Which row to look at first?
myRow = 2
lastRow = Cells(myRow, myCol).End(xlDown).Row
myValue = Cells(myRow, myCol)
startRow = myRow
Application.ScreenUpdating = False
For i = myRow + 1 To lastRow + 1
  If Cells(i, myCol).Value <> myValue Then
  'New group begins
Range(startRow + 1 & ":" & i - 1).EntireRow.Group
  startRow = i
  myValue = Cells(i, myCol).Value
  End If
  If myValue = "" Then
  'blank cell
Exit For
  End If
Next i
Application.ScreenUpdating = True
End Sub

Thanks so much for you help.

John

 

[jproffer]

Just at a quick glance, maybe try changing this

For i = myRow + 1 To lastRow + 1

to this:

For i = myRow + 1 To lastRow - 1

to exclude the top and bottom rows. Or this:

For i = myRow To lastRow - 1

to exclude only the last row and include the top row.

 

[jpblev]

Thanks but neither options gave me what I needed. I'm unable to upload/attach images here but, as an example:


Row 1 Col A x Col B x1
Row 2 Col A x Col B x2
Row 3 Col A x Col B x3
Row 4 Col A x Col B x total
Row 5 Col A y Col B y1
Row 6 Col A y Col B y2
Row 7 Col A y Col B y total

The current macro is grouping Rows 2,3,4 and then grouping Rows 6,7. I need to group Rows 1,2,3 and the group 5,6.