Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In Excel 2003 VBA I'm trying to insert sum totals on several columns based on the value in another column(W) . However, when I code the insert row,
    the row is inserted across all columns and I only want the row inserted across columns V To AC


    [codebox]
    Sub DoSubTotal()

    Dim WshTarget As Worksheet
    Dim rng As Range
    Dim k As Integer
    Dim kntdups As Integer
    Application.ScreenUpdating = False
    Set WshTarget = Worksheets("SSI")
    WshTarget.Activate

    Set rng = Range("W:W")
    k = 5
    kntdups = 0
    Do While rng.Cells(k).Value <> ""
    Do
    If rng.Cells(k) = rng.Cells(k + 1) Then
    kntdups = kntdups + 1
    k = k + 1
    Else
    If kntdups >= 1 Then
    With rng
    .Cells(k + 1).EntireRow.Insert
    .Cells(k + 1) = "Group Total " & .Cells(k)
    .Cells(k + 1).Offset(0, 2).FormulaR1C1 = "=SUM(R[-" & kntdups + 1 & "]C:R[-1]C)"
    .Cells(k + 1).Offset(0, 3).FormulaR1C1 = "=SUM(R[-" & kntdups + 1 & "]C:R[-1]C)"
    .Cells(k + 1).Offset(0, 4).FormulaR1C1 = "=SUM(R[-" & kntdups + 1 & "]C:R[-1]C)"
    .Cells(k + 1).Offset(0, 5).FormulaR1C1 = "=SUM(R[-" & kntdups + 1 & "]C:R[-1]C)"
    .Cells(k + 1).Offset(0, 6).FormulaR1C1 = "=SUM(R[-" & kntdups + 1 & "]C:R[-1]C)"
    End With
    kntdups = 0
    k = k + 2
    Else
    kntdups = 0
    k = k + 1
    End If
    End If
    Loop Until kntdups = 0
    Loop
    End Sub
    [/codebox]

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

    .Cells(k + 1).EntireRow.Insert

    explicitly tells Excel to insert an entire row. If you want to insert cells in columns V to AC only, change it to

    Range("V" & (k + 1) & ":AA" & (k + 1)).Insert Shift:=xlShiftDown

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    WOW. It Worked Great !!

Posting Permissions

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