How to group blank rows

[jltromb]

Hello!

I have a sheet full of data. In column A there are many rows with blank cells. I'd like to collapse (group) them up to the previous row that contains data.

The goal is to have all data showing where there is a value in Column A. The user could then click the "+" next to that row and expand the data (I have an ADO recordset placing the data appropriately, so expanded rows would be related to the row in view).

I'm sure the answer lies in Range.Rows.Group but I haven't been able to make it work - any ideas?

Thanks,
Jim

 

[jltromb]

As a follow up - this code is working well but the "+" isn't on the line that corresponds to the data to be expanded. It's on the row below it!

    For i = 1 To (rs.recordCount * 2)
        If Range("A" & i).Value = "" Then
            Range("A" & i).Rows.Group
        End If
    Next i

 

[nithin49]

As a follow up - this code is working well but the "+" isn't on the line that corresponds to the data to be expanded. It's on the row below it!

    For i = 1 To (rs.recordCount * 2)
        If Range("A" & i).Value = "" Then
            Range("A" & i).Rows.Group
        End If
    Next i

Thanks. Your code helped me a lot.

Data->Outline options->uncheck " Summary rows below detail" box.

"+" will be on the line that data needs to be expanded