Changing VBA for new data format

sin2190

New Member
Joined
Aug 17, 2016
Messages
6
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)


Code:
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:eek:" & LastRow + 1)
With Rng
.AutoFilter Field:=1, Criteria1:="="
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Range("p1").Cut Range("o1")
End Sub
Code:
 
Last edited:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
in a new file take a row from the old file (unedited) and put a row from the new file (unedited) next to it and see what the difference is, as we can see you are inserting columns
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,497
Members
414,072
Latest member
2020914

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top