Delete rows based on fill color

**schueyisking** · 08-21-2008, 09:57 AM

hi,

i found a link to my problem in the subject title below,

http://www.ozgrid.com/forum/showthre...t=45580&page=2

The guy at the end of the post says he got it working in the end, but no one seems to have posted the final solution - could someone tell me if the solution is actually in the thread, and if so where!

My problem is outlined by this guy - basically i want to be able to delete rows in a range that have no fill color, and so keep the ones that are colored.

thanks

**MickG** · 08-21-2008, 10:43 AM

Hi, In simple terms this should do what you want.

Dim Last As Integer, Del As Integer
Last = Range("A" & Rows.Count).End(xlUp).Row

For Del = Last To 1 Step -1
    If Cells(Del, "A").Interior.ColorIndex = xlNone Then
        Rows(Del).EntireRow.Delete
    End If
Next Del

Regards Mick

**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:50 AM

awesome it works fine now,the original code.

thank you!

**schueyisking** · 08-22-2008, 05:54 AM

Actually i've discovered a problem,

The code works with cells highlighted with a fill colour, but unfortunately doesn't work for cells which have a color due to a conditional format. Is there any way to modify the code so that it detects the conditionally formatted colour, and deletes that row instead?

Or if thats not possible, a button, that when pressed, automatically converts the conditionally formatted color to a fill color of the same color, if that makes sense. So if the cell is Red, due to a conditional format, a button is pressed, and it removes the conditional formatting but keeps the color as red - Then i could use the code as normal.

Just this final problem to solve then my spreadsheet is complete!
Please help!