Delete multiple rows based on cell color

[mightymorgs]

I'm attempting to write my first VBA code. I have several worksheets in one workbook and I need to apply the code to 8 specific worksheets based on worksheet name. Within each worksheet, I need to delete several rows based on fill color (there's no conditional formatting). I have the basic code below that seems to work when I apply it to just one worksheet but the colored rows are spread throughout the sheets and I can't figure out how to loop it correctly so that it deletes all of the required rows. It also doesn't seen to work when I add in the other worksheet names. I'm not opposed to replicating the code for each worksheet but there are 8 of them and I have a hunch that there's an easier way.

To sum it up, I need this code to apply to 8 worksheets within the same workbook and it needs to loop through the worksheet to delete every row with specific fill colors.


Sub DeleteRows()


With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

Worksheets("Sheet1").Select
Dim cell As Range
For Each Row In Selection
If Row.Interior.Color = RGB(144, 238, 144) Then
Row.EntireRow.Delete
End If
Next Row

For Each Row In Selection
If Row.Interior.Color = RGB(175, 238, 238) Then
Row.EntireRow.Delete
End If
Next Row


With Application
.Calculation = xlCalculationManual
.EnableEvents = True
.ScreenUpdating = True
End With


End Sub


Thanks in advance!

[Bob Phillips]

I have nothing to test it on, but try this


Sub DeleteRows()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long

With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

For Each ws In ThisWorkbook.Worksheets

lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1

If ws.Cells(ws.Rows.Count, "A").Interior.Color = RGB(144, 238, 144) Or _
ws.Cells(ws.Rows.Count, "A").Interior.Color = RGB(175, 238, 238) Then

ws.Rows(i).Delete
End If
Next i
Next ws

With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

[mightymorgs]

Thanks for the speedy reply.

Two additional requests:

1) The code looks promising but when I copied it over into VBA and tried to run it, it didn't do anything to the worksheets. I didn't get any errors, it just didn't delete anything either.

2) I need to identify the worksheets individually. There are a total of 17 worksheets in this one workbook and I only need to run this code on 8 of them. How do I identify those 8 worksheets?

If it helps to have an example, I stripped out the data on two of the worksheets and left the colored rows and attached it here.
29248

[Bob Phillips]

The reason my code did nothing is that there is no data for me there to find the last row (you have to delete last row to first, else you get problems).

I loaded the first column with some values to test my code, and copied the first two sheets.

I have created an example with a control sheet where you id the sheets you want to target, and i also added a table where you can put the colours you want to target - full flexibility.

Look at sheets Test1 Base (2) and Test2 Base (2), these were the ones I tested on, you will see no coloured rows. I altered the sheets table to look for Test1 Base and Test2 Base, you can test it on these two.

Hopefully you can make something of this.