VBA SUMIF then Delete Duplicate Row

Vote count:

0

I am trying to create a macro (I know that a Pivot can also accomplish it; however, I need a macro). I need a macro to Sumif the Values in Col D if it matches the Criteria in Col A. Then I want to delete all the other values and only leave the sumif value.

For Example

Col A ColD AB 10 CD 5 FG 25 HI 100 AB 50 FG 0 OP 500 HI 80 OP 66

Then Col A ColD AB 60 CD 5 FG 25 HI 180 OP 566

Currently I am using the following code but its not working

Dim Sh As Worksheet
Dim LastRow As Long
Dim Rng As Range
Set Sh = Worksheets(1)
Sh.Columns(5).Insert
LastRow = Sh.Range("A65536").End(xlUp).Row
With Sh.Range("A1:A" & LastRow).Offset(0, 4)
    .FormulaR1C1 = "=IF(COUNTIF(R1C[-4]:RC[-4],RC[-4])>1,"""",SUMIF(R1C[-4]:R[" & LastRow & "]C[-4],RC[-4],R1C[-1]:R[" & LastRow & "]C[-1]))"
    .Value = .Value
End With
Sh.Columns(4).Delete
Sh.Rows(1).Insert
Set Rng = Sh.Range("D1:D" & LastRow + 1)
With Rng
    .AutoFilter Field:=1, Criteria1:="="
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

Any assistance will be greatly appreciated. Thanks

asked 45 secs ago

VBA Noob

6

VBA SUMIF then Delete Duplicate Row