Good evening fellow night owls. So I have this macro that find duplicates values in a row (phone numbers) and sums up the the value in another row (rebate amount) while also deleting one of the duplicate rows. Now there seems to be an update to the way the file has been set up and now the macro no longer works. I've been trying to alter the VBA for the last 3 hours but to no avail. I've tried moving the columns around but for some reason it still isn't working when I do it on the original file. Below is the Macro I'm currently using. If anyone can tell where the issue is so that I may figure it out next time on my own, I'd really appreciate it.
So basically:
G= Phone number
H= Promo Name
P=Rebate Amount (before it was O)
Sub Combine()
Dim Sh As Worksheet
Dim LastRow As Long
Dim Rng As Range
Range("o1").Copy Range("p1")
Set Sh = ActiveSheet
Sh.Columns(16).Insert
LastRow = Sh.Range("g65536").End(xlUp).Row
With Sh.Range("g1:g" & LastRow).Offset(0, 9)
.FormulaR1C1 = "=IF(COUNTIF(R1C[-9]:RC[-9],RC[-9])>1,"""",SUMIF(R1C[-9]:R[" & LastRow & "]C[-9],RC[-9],R1C[-1]:R[" & LastRow & "]C[-1]))"
.Value = .Value
End With
Sh.Columns(15).Delete
Sh.Rows(1).Insert
Set Rng = Sh.Range("o1" & LastRow + 1)
With Rng
.AutoFilter Field:=1, Criteria1:="="
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Range("p1").Cut Range("o1")
End Sub
So basically:
G= Phone number
H= Promo Name
P=Rebate Amount (before it was O)
Code:
Dim Sh As Worksheet
Dim LastRow As Long
Dim Rng As Range
Range("o1").Copy Range("p1")
Set Sh = ActiveSheet
Sh.Columns(16).Insert
LastRow = Sh.Range("g65536").End(xlUp).Row
With Sh.Range("g1:g" & LastRow).Offset(0, 9)
.FormulaR1C1 = "=IF(COUNTIF(R1C[-9]:RC[-9],RC[-9])>1,"""",SUMIF(R1C[-9]:R[" & LastRow & "]C[-9],RC[-9],R1C[-1]:R[" & LastRow & "]C[-1]))"
.Value = .Value
End With
Sh.Columns(15).Delete
Sh.Rows(1).Insert
Set Rng = Sh.Range("o1" & LastRow + 1)
With Rng
.AutoFilter Field:=1, Criteria1:="="
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Range("p1").Cut Range("o1")
End Sub
Code:
Last edited: