Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

How To Insert 3 Rows and Total

Latest post Wed, Aug 13 2008 11:22 AM by Norseman. 2 replies.
  • Wed, Aug 13 2008 8:30 AM

    • Norseman
    • Top 200 Contributor
    • Joined on Wed, Aug 13 2008
    • Posts 2
    • Points 26

    How To Insert 3 Rows and Total

     I've been knocking myself out since yesterday trying to figure something out.  I have some self-taught experience with VBA macros in Excel, but just can't get this.  And I bet it's simple.  Here's the deal:

    I have a monthly spreadsheet that lists users and their cell phone expenses.  There is typically 4 - 6 rows for each user.  I want to write a macro that will look at the cell that contains the name, compare it to the cell below it.  Then if they match, move down one cell and compare again.  Then when it fines a difference, add 3 rows, put a subtotal under the expense cells; and then go back to analyzing the name cells looking for the next break and resume.

    Is that confusing?  Would anybody be able to help me out with this ...I'd be very greatful!

    Thanks

    • Post Points: 21
  • Wed, Aug 13 2008 9:23 AM In reply to

    Re: How To Insert 3 Rows and Total

    Public Sub ProcessData()
    Dim i As Long
    Dim LastRow As Long

        With Application
           
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
        End With
       
        With ActiveSheet
           
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = LastRow + 1 To 3 Step -1
           
                If .Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then
               
                    .Rows(i).Resize(3).Insert
                    .Cells(i, "B").FormulaR1C1 = _
                        "=SUMIF(R2C1:R[-1]C,""" & .Cells(i - 1, "A").Value & """,R2C:R[-1]C)"
                '    i = i + 1
                End If
            Next i
           
        End With
       
        With Application
           
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
        End With
       
    End Sub

    Regards

    Bob

    • Post Points: 21
  • Wed, Aug 13 2008 11:22 AM In reply to

    • Norseman
    • Top 200 Contributor
    • Joined on Wed, Aug 13 2008
    • Posts 2
    • Points 26

    Re: How To Insert 3 Rows and Total

     Thanks!!

    • Post Points: 5
Page 1 of 1 (3 items) | RSS
Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.