Repeat until no same value exists?

[pooley100777]

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 but.....as 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

 

[Comfy]

Are you using xl2003?

 

[pooley100777]

2007 unfortunately!

 

[Comfy]

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.

 

[pooley100777]

Columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("J1").Select
ActiveCell.FormulaR1C1 = "Val"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<0,RC[-1]*-1,RC[-1])"
Range("J2").Select
Do
ActiveCell.Offset(1, 0).Offset(0, 0).Select
If ActiveCell = "" Then
Selection.FillDown
End If

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

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

Range("g2").Select
Do
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
ActiveCell.Rows("1:2").EntireRow.Select
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?

 

[Comfy]

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?

 

[pooley100777]

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?

 

[Comfy]

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.

 

[pooley100777]

Nom	F/D	Div	Act	Act No.	Period	Batch Type	Description	Financial Value	Val
B153	ENT	GN	EU	C017	8	ACCL	Description 1	5308.16	5308.16
B153	FDR	J8	H2	0189	8	ACCL	Description 2	7204.00	7204.00
B153	ENT	GN	EU	E978	8	ACCL	Description 3	8206.12	8206.12
B153	FDR	J8	H2	0189	8	ACCL	Description 4	9999.00	9999.00
B153	ENT	GN	EU	C017	8	ACCL	Description 5	-5308.16	5308.16
B153	FDR	J8	H2	0189	8	ACCL	Description 6	-7204.00	7204.00
B153	ENT	GN	EU	E978	8	ACCL	Description 7	-8206.12	8206.12
B153	FDR	J8	H2	0189	8	ACCL	Description 8	-9999.00	9999.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>
</tbody>

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

 

[Comfy]

Thanks Pooley, that's a lot of help.