CODEWITHSUNDEEP
excelvba
IainW
IainW

Reputation: 53

Excel VBA - Add row & make active

Good evening

Please see the attached image for an example of my data. The strings in column A are grouped together.

enter image description here

The below code is a WIP to achieve the following...

  1. Find the last occurrence of each delivery location & add a new row after.
  2. In the newly created row, in the columns named Header11-14, add a formula to total the values in the above rows
  3. Do some formatting

So far it adds the new row after each delivery location but what I can't figure out is how to add the sum formula. I know how to add the string but I can't figure out how to reference the cells above...

enter image description here

The image above what i'm trying to achieve.

Sub insertRow_totals()
Dim changeRow, counter As Integer

counter = 2

While Cells(counter, 1) <> ""
    If Cells(counter, 1) <> Cells(counter - 1, 1) Then
        Rows(counter).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

        counter = counter + 2
    End If
    counter = counter + 1
Wend

Rows(2).EntireRow.Delete
End Sub

Upvotes: 2

Views: 153

Answers (1)

h3n
h3n

Reputation: 898

you need to count how many rows with the same name there are (or remember the row index of the first one), then something like this should work

Sub insertRow_totals()
Dim changeRow, counter As Integer

counter = 2
FirstRow = 2

While Cells(counter, 1) <> ""
    If Cells(counter, 1) <> Cells(counter - 1, 1) Then
        Rows(counter).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    For i = 11 To 14
        ActiveSheet.Cells(counter, i).Formula = "=SUM(" & Cells(FirstRow, i).Address & ":" & Cells(counter - 1, i).Address & ")"
    Next i

    counter = counter + 1
    FirstRow = counter
End If
    counter = counter + 1
Wend

Rows(2).EntireRow.Delete
End Sub

Upvotes: 1

Related Questions