VBA SUMIF then Delete Duplicate Row

[khalari808]

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 appreciated tremendously.

 

[Deepak]

hi @khalari808 ,
Pls take the pain to upload a sample workbook with desired output/result.

 

[prasaddn]

Hi,

I ran your macro and it is working fine, what and where is it erroring for you?

Regards,
Prasad DN

 

[khalari808]

Sorry una Momenta. You're right its working but something is throwing it off. Let me check and retry