Something wrong with this code.

[BenNV]

I have the following piece of code, which I was hoping would delete any lines when matched with any other string in Column 1.

Sub Del_Entries()

Dim Store_Ins As String

Set FixWS = Workbooks("test.xls").Worksheets("ion")

rr = 1

Store_Ins = Cells(rr, 1).Value

rr = rr + 1

While (FixWS.Cells(rr, 1).Value <> "")
If (FixWS.Cells(rr, 1).Value = Store_Ins) Then
FixWS.Cells(rr, 1).EntireRow.Delete Shift:=xlShiftUp
Else
Store_Ins = Cells(rr, 1).Value
End If
rr = rr + 1
Wend

End Sub



So, here's some data to work with:

RRR/OOO 07/05/02
RRR/OOO 07/05/02
RRR/OOO 07/05/02
RRR/OOO 07/05/02
FFF/MMM 07/05/02
FFF/MMM 07/05/02
FFF/MMM 07/05/02
FFF/MMM 07/05/02
RRR/OOO 07/05/02
RRR/OOO 07/05/02
RRR/OOO 07/05/02
RRR/OOO 07/05/02
RRR/OOO 07/05/02
RRR/OOO 07/05/02
RRR/OOO 07/05/02
XXX/WWW 07/05/02
XXX/WWW 07/05/02
XXX/WWW 07/05/02
XXX/WWW 07/05/02
XXX/WWW 07/05/02
XXX/WWW 07/05/02



This should only leave RRR/OOO, FFF/MMM, XXX/WWW and their relative date.

For some reason it doesn't perform the task in one go, I have to run it a few times. Can you help?

 

[lenze]

Chip Pearson's code at this address produces the desired result when run on your sample data.

http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows

 

[Mark O'Brien]

I've "fixed" your code, sort of, but there is an error in your loop. This loop only compares for identical values that are in continuous rows, so the result is:

RRR/OOO 07/05/02
FFF/MMM 07/05/02
RRR/OOO 07/05/02
XXX/WWW 07/05/02


Here is the modified code:

<pre>
Sub Del_Entries()

Dim Store_Ins As String

Set FixWS = Workbooks("test.xls").Worksheets("ion")

rr = 1

Store_Ins = Cells(rr, 1).Value

rr = rr + 1

While (FixWS.Cells(rr, 1).Value <> "")
If (FixWS.Cells(rr, 1).Value = Store_Ins) Then
FixWS.Cells(rr, 1).EntireRow.Delete Shift:=xlShiftUp
Else
rr = rr + 1
Store_Ins = Cells(rr, 1).Value
End If
Wend

End Sub</pre>

You can mess about with it to get it to do what you want, or if you want a suggestion, just repost.

HTH