Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Code to insert rows and sum (xppro ms2003)

    Hi

    We have a requirement to copy data from our database and run some analysis on it. The data is extracted into groups with no empty rows between each group (see attached).

    What we wish to do is to insert 2 rows after each group and then sum columns C & D. While we can do this manually doing it on 5 or 6 batches of data, with up to 20 groups, every month is a bit of a chore.

    I have tried to record a macro to do this but it keeps using absolute cell references and I can't figure out how to make them dynamic.

    Can anyone supply some code that we could assign to a keystroke to accomplish this?

    cheers
    Attached Files Attached Files
    cheers

    Phil Carter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code to insert rows and sum (xppro ms2003)

    Try this:
    <code>
    Sub InsertRowsAndSum()
    Dim r1 As Long
    Dim r2 As Long
    Dim rMax As Long
    rMax = Range("C65536").End(xlUp).Row
    r1 = rMax
    Do
    r2 = r1
    Do While Range("A" & r2) = ""
    r2 = r2 - 1
    Loop
    Range("A" & (r1 + 1) & ":A" & (r1 + 2)).EntireRow.Insert
    Range("A" & (r1 + 1)) = "Total"
    Range("C" & (r1 + 1) & "" & (r1 + 1)).FormulaR1C1 = _
    "=SUM(R<!t>[" & (r2 - r1 - 1) & "]<!/t>C:R<!t>[-1]<!/t>C)"
    r1 = r2 - 1
    Loop Until r1 < 3
    End Sub</code>

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Code to insert rows and sum (xppro ms2003)

    Hans

    Once again thanks very much. Works a treat.

    This will please our staff enormously
    cheers

    Phil Carter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •