Excel macro code that will move down in subtotals? 1

[ls62]

Hi,
I have a excel spreadsheet that I have sorted and subtotaled. I'm trying to write a macro that when the subtotal 2 level is selected I can move down a given column and select data on that row based upon the values in that subtotal row. If the subtotals have a header in row 1 and the subtotals in rows 6,10,15,25 (other rows are hidden) how do I get the macro to move down from b1 to b6 to b10. If I use a offset(1) command it selects row 2 even though its not visable?

Thanks for any help.

LEE

 

[SkipVought]

Hi,

Sub NextSubtotal()
    Dim rg As Range
    Set rg = ActiveCell
    i = 1
    With rg
        Do
            If Left(.Offset(i, 0).Formula, 9) = "=SUBTOTAL" Then
                .Offset(i, 0).Select
                Exit Sub
            End If
            If IsEmpty(.Offset(1, 0).Value) Then Exit Sub
            i = i + 1
        Loop
    End With
End Sub

this should work
Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ls62]

Skip,

I think this will work! I'll give it a try.

Thanks for your help!

LEE