Delete rows based on fill color

**schueyisking** · 08-21-2008, 11:43 AM

hi mick,

certainly learning something new today!

i created the command button, followed the instructions, ran it, but it still didn't seem to do anything.
i've attached the file i was testing it on.
as you can see i just made some rows up to column H, with some that are red, and ones with no fill, in the hope that when i pressed the button, rows 4,5, and 9 would dissapear.

im obviously doing something completely wrong, would appreciate your input on it.

cheers.

**SOS** · 08-21-2008, 12:10 PM

Hi schueyisking,

That code would work if the cells in column A had data in them. The reason it doesn't work is that the line

Last = Range("A" & Rows.Count).End(xlUp).Row

ends up as being 1 because that line is looking for data in Column A.

If, however, you selected all the cells you need in Column A and then run this code it should do what you want:

Private Sub CommandButton1_Click()
    For i = Selection.Rows.Count To 1 Step -1
    If Range("A" & i).Interior.ColorIndex = xlNone Then
       Range("A" & i).EntireRow.Delete
    End If
   Next i
End Sub

**MickG** · 08-21-2008, 12:15 PM

Hi, The reason it's not working is because you have no Data in column "A".
If you want it to work that way change the Variable "Last" to a row number like this:-
Last = 50 (Or Whatever)
NB I've just seen you already got the solution fron SOS!!
Regards Mick

**schueyisking** · 08-22-2008, 04:48 AM

Oooooh right I see.

Yeh I would actually be using this on rows with data, so the original code will work then, sorry I didn't realise it was dependent on looking for information in the cells (I can't read code at all obviously).

Thanks, i'll give it a go now, should work.

much appreciated.