Delete rows based on fill color

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

hi ,

thanks for your response - i inserted a module into the workbook and put the code in.

How do i use it though? I can't see a way of using it from looking at the code.

Sorry for being a noob.

**MickG** · 08-21-2008, 11:28 AM

Hi, Have a read of this , it should help !

Inserting a Command Button in worksheet and Allocating Code

Open your workbook at the Sheet Number with your Data that you want to Manipulate.
Click View, Toolbars, Control ToolBox,---- Control ToolBox Menu Appears on sheet.
Slide you cursor over the Tool Box (Tool Tip Text) until you find a Command Button.
Click It then click Somewhere on the sheet.-- Command Button appears on sheet.
The Command Button will have handles round its edge, This is so you can Position it on the sheet.
Click the command Button "Hold the Mouse Click Down" and Drag the Command Button to where you want it.
Double Click the Command Button, The VB Editor window will appear.
You can also open the Editor By clicking Alt + F11, but if you double click the Command Button the editor will open in the procedure relating specifically to your Command Button.
If the VB Editor window has two panes the right pane is where you must paste your code.
The left pane can be "Project Window" or "Properties Window, Click (Ctrl+R) if not showing.

Paste your code just under the words "Private Sub CommandButton1_Click() " in the Right hand pane.
If you have done this correctly. Scroll to the bottom of the code and you should see the words "End Sub"
On the VB Window, Toolbar you will see a Green Triangular shapes icon,.
This is to change the VB Editor mode from "Run Mode" to "Design Mode "
Click this Icon, The Small blue square to its left will change from light blue to dark blue, or Vice Versa. Before you close the Editor make sure this Square is "Dark Blue" i.e. (Reset)
Sometimes the it will appear Reset when it is not, that why I usually put a message at the bottom of the code, to know if is run or not.(You do not have a msgbox in your code)
Close the Editor. Select the Command Button and Click it.
When the code Runs the Msgbox should appear With The Message "Transfer Complete" ,if this doesn't happen Open The VB Editor "Alt + F11" and click the "Reset ( Blue Square) and or The "Green Triangle" on the Tool bar . The Blue Square should be "Dark Blue ", in order to run the code..
NB:- If you want to get back into this specific code through the Command Button.
Get the Controls ToolBox menu back on the screen, Click the green triangle, When you slide the cursor over your Command button , The cursor shape will change to a "Arrow Headed Cross" , you will then be able to double click it to view your code.
If you prefer, you can forgo all this hassle by putting the code in an MT macro, with a key combination like (Ctrl+"A") to run it.
If you still get a problem , call back
Regards Mick

**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.