Group based on cell value

[rinxman]

I have a file produced from a SQL query which has a volatile number of rows each day. On of the columns is the sortkey which tells the query uses to list the data in the right order. I am now being asked to group the data in each category. My thought is the easiest way to do this is to use the sortkey column, but since the number of rows changes daily, I need a macro that will allow for the varying row numbers.

Below is a list of the values in that sortkey column. The first 3 numbers designate the category and the last 2 would be the subcategories that need to be grouped. So with the values below, 40300 would be the category header, while 40301, 40303, 40306, 40308, 40309, 40310, 40312 would be the subcategories.

40100
40200
40300
40301
40303
40306
40308
40309
40310
40312
40400
40402
40500
40501
40503
40506
40508
40509
40512

<colgroup><col></colgroup><tbody>
</tbody>

How can I write code that will cycle through this column and set up the grouping?

Thanks.

 

[NeonRedSharpie]

Sub groupingRows()

    Dim endRow As Long
    endRow = Cells(Rows.Count, "A").End(xlUp).Row


    Dim startRow As Integer


    For x = 1 To endRow
        If Right(Cells(x, 1), 2) = "00" And Left(Cells(x, 1), 3) = Left(Cells(x + 1, 1), 3) Then
            If startRow <> 0 Then
                Range(Cells(startRow, 1), Cells(x - 1, 1)).EntireRow.Group
                startRow = x + 1
            Else
                startRow = x + 1
            End If
        End If
    Next x
End Sub

This one kind of hurt my head...so try it out, see if it works, but I bet there's a bug or two.

 

[rinxman]

Thank you. It didn't do anything. The sortkey column is column Y so I changed this line:

endRow = Cells(Rows.Count, "Y").End(xlUp).Row

Is that correct? Also, there are a couple of tabs on the report. Does a tab name need to be specified somewhere?
</pre>

 

[NeonRedSharpie]

Sub groupingRows()

    Dim endRow As Long

    Dim startRow As Integer
    endRow = .Cells(Rows.Count, "Y").End(xlUp).Row
    With sheets("SHEET NAME HERE")

    For x = 1 To endRow
        If Right(.Cells(x, 25), 2) = "00" And Left(.Cells(x, 25), 3) = Left(.Cells(x + 1, 25), 3) Then
            If startRow <> 0 Then
                 .Range(.Cells(startRow, 25), .Cells(x - 1, 25)).EntireRow.Group
                startRow = x + 1
            Else
                startRow = x + 1
            End If
        End If
    Next x

    End With
End Sub

Try that. I had poor code practice when I wrote it.

 

[rinxman]

It has been a while since I've worked in VBA. I can't believe I missed the (x,25) reference for column Y.

This almost worked - about 75% correct. It grouped rows with these sort values:

11509

40100

40200

40300

<colgroup><col></colgroup><tbody>
</tbody>

Then it included these groups with the grouping of values starting with "405":

40600

40700

40900

41000

41200

<colgroup><col></colgroup><tbody>
</tbody>

Finally, it missed the last grouping completely. The last 2 rows should have been grouped:

41900

41906

41912

<colgroup><col></colgroup><tbody>
</tbody>

 

[NeonRedSharpie]

Sub groupingRows()

    Dim endRow As Long


    Dim startRow As Integer


    With Sheets("Sheet5")
    endRow = .Cells(Rows.Count, "Y").End(xlUp).Row
    For x = 1 To endRow
        If .Cells(x + 1, 25) = "" Then GoTo found:
        If Right(.Cells(x, 25), 2) = "00" And Left(.Cells(x, 25), 3) = Left(.Cells(x + 1, 25), 3) Then
        
            If startRow <> 0 Then
found:
                 .Range(.Cells(startRow, 25), .Cells(x - 1, 25)).EntireRow.Group
                startRow = x + 1
            Else
                startRow = x + 1
            End If
        End If
    Next x


    For x = 1 To endRow
        If Right(.Cells(x, 25), 2) = "00" Then .Cells(x, 25).EntireRow.ClearOutline
    Next x




    End With
End Sub

This is pretty much the definition of a hack, but it works. I can't figure out the hole in my logic to group following "00" together. And I can't replicate your grouping after 11509...

 

[rinxman]

I think the issue, except for the last incident, might be that it is looking to group every row. I noticed that where it messed up is where there were rows that had no subcategories.

 

[rinxman]

Looks like I was replying when you were. Your suggestion worked except for my last row. Every visible row ends with 00 except my last row which wasn't grouped.

 

[rinxman]

I changed this row:

For x = 1 To endRow + 1

And it works. It actually groups an extra blank row but that will work. I REALLY appreciate your help on this. I've been out of the VBA loop too long.
</pre>