Variable Rows Macro

chenm23 · Mar 13th 2007

So I have about 4000+ rows of data that basically contain one column that has a name, and then a number associated with that person. But the number of rows for each person is variable. For example, in column A we have Bob Smith from row 1 to row 5, Mary Jane from row 6 to row 20, Tom White from row 21 to row 24; then in column B we have numbers that are associated with each person. So what needs to happen is every time the name of the person in column A changes, there needs to be a row added below it and then a total of all their number is column B placed underneath. I guess the tricky part of it is that it's not a set number of rows...

Please help, it's greatly appreciated!

**Ger Plante** · Mar 13th 2007

Re: Variable Rows Macro

Not sure i understand... am I right in saying that the last row is just a total for everything in column B?

Maybe if you posted a cut down example of what you want to happen it would help...

Ger

chenm23 · Mar 13th 2007

Re: Variable Rows Macro

EX:

Bob Smith 22
Bob Smith 100
Bob Smith 200
Total 222
Mary Jane 40
Mary Jane 80
Total 120

Robert B · Mar 13th 2007

Re: Variable Rows Macro

Hi

Try Subtotals, at each Change in Name(?), function Sum on column B

Robert

**Ger Plante** · Mar 13th 2007

Re: Variable Rows Macro

This appears to work ok...

Code

Public Sub test()


Dim lLast_Total_Row As Long
Dim lRow_Count As Long


lLast_Total_Row = 1
lRow_Count = 2


With ActiveSheet
    Do
        If .Cells(lRow_Count, 1) <> .Cells(lRow_Count - 1, 1) Then


            .Range(Cells(lRow_Count, 1), Cells(lRow_Count, 2)).Insert shift:=xlDown
            .Cells(lRow_Count, 1).Value = "Total " & .Cells(lRow_Count - 1, 1)
            .Cells(lRow_Count, 2).Formula = "=Sum(B" & lLast_Total_Row & ":B" & lRow_Count - 1 & ")"
            lRow_Count = lRow_Count + 1
            lLast_Total_Row = lRow_Count
        End If


        lRow_Count = lRow_Count + 1
        
    
    Loop Until Trim(.Cells(lRow_Count, 1).Value) = ""
    .Range(Cells(lRow_Count, 1), Cells(lRow_Count, 2)).Insert shift:=xlDown
    .Cells(lRow_Count, 1).Value = "Total " & .Cells(lRow_Count - 1, 1)
    .Cells(lRow_Count, 2).Formula = "=Sum(B" & lLast_Total_Row & ":B" & lRow_Count - 1 & ")"
End With


End Sub

Display More

HTH
Ger

Variable Rows Macro

Participate now!

Share