This should be simple but...

[Jeff]

I have a spreadsheet with thousands of lines of data
I need to remove some lines of data

The rule is...
If any cell in column A has a background colour that's not the default
(blank) then delete the whole row.

I know this should be a simple loop but recording a macro doesn't seem to
give me any clues. Any help would be appreciated.

Jeff

 

[Guest]

Jeff, if I understood you correctly this should do the trick:

Sub erase_rows()
lastrow = ActiveSheet.UsedRange.Rows.Count
For i = lastrow To 1 Step -1
cell = "A" & i
Range(cell).Select
If Selection.Interior.ColorIndex <> xlNone Then
Rows(i).Select
Selection.Delete Shift:=xlUp
End If
Next i
End Sub

 

[KRCowen]

Jeff

Something like this should work. Cells with the default background (pattern?)
have the PatternColorIndex of -4142, not my first guess but I was able to use
the immediate pane to find out). Select the applicable rows and run this
simple code.

Sub test()

Dim cl As Range

For Each cl In Selection
If Not (cl.Interior.PatternColorIndex = -4142) Then cl.EntireRow.Delete
Next cl

End Sub

Good luck.

Ken
Norfolk, Va

 

[Jeff]

Thanks Ken

That worked reasonably

For some reason, deleting rows from the selection made the macro "jump"
rows, but I got round that by simply running the macro 50 times!

Job done thanks.
I'll now go away and brush up on how to retrieve cell properties.


Jeff