Repeat until no same value exists?


New Member
Mar 27, 2013
Hi all,

Could someone please help me with the following problem as i am getting to the point where i am looking for a workaround and i really dont want that.

I have data that needs to be deleted when 2 values match. I have managed to do this the data is not together i have to sort on the column first and then run the marco to delete the cells.

I could just keep on copying the macro down for it to repeat itself but surley there is another way?

What i want to do is for the marco to carry on until it cannot find anymore same value figures.

I have looked at myrow = active.cell but i am not very good at this VBA stuff.

your help would save me my hair! many thanks

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Could you post the macro you are using?

I think 2007 has the remove duplicates function built-in.

Look in the Data Tab in the Data Tools section.
Upvote 0
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.FormulaR1C1 = "Val"
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<0,RC[-1]*-1,RC[-1])"
ActiveCell.Offset(1, 0).Offset(0, 0).Select
If ActiveCell = "" Then
End If

Loop Until ActiveCell.Offset(1, -1) = ""

ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("J2:J5000"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort

If ActiveCell.Value = "ACCL" Then
If ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(1, 3).Value Then
If ActiveCell.Offset(0, 2).Value + ActiveCell.Offset(1, 2).Value = 0 Then
Selection.Delete Shift:=xlUp
ActiveCell.Offset(-1, 6).Select
End If
End If
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(1, 0) = ""

The problem is that after i have deleted the cells that match, i will need to sort again on column "J" and then run the above again.

Hope this makes sense?
Upvote 0
Did you look at the remove duplicates function?

When using a macro to delete values in a range you should loop backwards otherwise you will miss some values.

Could you post some sample data from your workbook so that I have something to work with?
Upvote 0
sorry i cant find it, but ther data would look like this.

Column A B C
Description Value Value 2
sdafasdf 100 100 These 2 lines would be deleted
dasfdfda -100 100 "" "" "" "" ""
ffssdafs 200 200 This line wouldnt because the formular is only looking at the 2 lines below
fdsasdd 300 300 Same goes for this one, meaning i would have to sort on column "C" and run again.
fadfdasf -200 200
gfsgfsg -300 300

Sorting on column "C" would work before the marco is run but once the macro runs i need to pick up any stragglers and the only way to do that is to sort on column "C" again and run the delete bit of the marco again?
Upvote 0
But your Macro refers to columns G, I & J

To avoid confusion, could you post a sample of these columns. Obviously you can change the values but keep the formatting.
Your macro seems to be comparing the Value in J & Row to J & Row + 1 and if they are the same then checking to see if the same rows in column I equal zero.

In your example you have posted columns A to C and you want to compare column B to C in the same row.

Because of the mismatch in information it's not very clear what you would like.
Upvote 0
Nom F/DDivActAct No.PeriodBatch TypeDescriptionFinancial ValueVal
B153ENTGNEUC0178ACCLDescription 15308.165308.16
B153FDRJ8H201898ACCLDescription 27204.007204.00
B153ENTGNEUE9788ACCLDescription 38206.128206.12
B153FDRJ8H201898ACCLDescription 49999.009999.00
B153ENTGNEUC0178ACCLDescription 5-5308.165308.16
B153FDRJ8H201898ACCLDescription 6-7204.007204.00
B153ENTGNEUE9788ACCLDescription 7-8206.128206.12
B153FDRJ8H201898ACCLDescription 8-9999.009999.00

<colgroup><col style="width: 29pt; mso-width-source: userset; mso-width-alt: 1426;" width="39"> <col style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;" width="35"> <col style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;" span="2" width="27"> <col style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;" width="53"> <col style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;" width="46"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" width="77"> <col style="width: 207pt; mso-width-source: userset; mso-width-alt: 10093;" width="276"> <col style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;" span="2" width="102"> <tbody>

Sorry try this, this is from column "A" to "J" hope it makes sense?
Upvote 0

Forum statistics

Latest member

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
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 "".
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