Deleting Rows in Excel with Strikethrough Fonts 1

[Edrondol]

I have a large spreadsheet that has duplicate entries. Normally, I could use the DeleteDuplicateRows() sub that I got from here (Thanks!), but in this case the dups are in strikethrough font. I wrote a small sub:

Sub TestingStrikethroughDelete()
Dim c As Range
For Each c In Selection
If c.Font.Strikethrough = True Then c.EntireRow.Delete
Next c
End Sub

It works fine if I run it four or five times, but for some reason it doesn't get all of the rows. It'll drop about half each time. What have I missed?


-Dave the Perpetually Confused

 

[SkipVought]

Hi,

The problem is that For Each...Next proceeds from TOP to BOTTOM. When you delete, you mess up row references. The solution in ROW DELETE is t proceed from the BOTTOM UP.

Sub TestingStrikethroughDelete()
Dim r as long
with selection
  for r = .rows.count + .row - 1 to .row step -1
    with cells(r, .column)
      If .Font.Strikethrough = True Then .EntireRow.Delete
    end with
  next
end with
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Edrondol]

My first response is, of course, D'OH!!! No kidding. Thanks for making my look foolish. Have a star.





-Dave the Perpetually Confused