Deleting highlighted cells within four columns of data - VBA?

[RedditGold]

Hi Excel Masters,

I'm looking for some code that allows me to delete cells within a given range that are conditionally formatted (they are highlighted yellow).

There are 4 columns (A:D) and there is 100,000 rows of data.

Does anyone know of a solution for this?

Thanks in advance!

[TMS]

You can filter on cell colour and select and delete the visible cells. You don't necessarily need VBA if it is a one off or infrequent task.

[RedditGold]

Hi TMS,

Whenever I try to filter the data, my version of Excel locks up. I think it may a result of how much data I'm trying to select as the CF has been applied to almost all of the 100000 rows... What you're recommending is a custom sort from the filter option, correct?

[TMS]

You could try setting calculation to manual before filtering.

[RedditGold]

The cells are no longer conditionally formatted, so I'm not sure if that helps or not, but they are now simply highlighted yellow.

[RedditGold]

Sub sbDelete_Rows_Based_On_Cell_Color()
Dim lRow As Long
Dim iCntr As Long
lRow = 24478
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 1).Interior.ColorIndex = 6 Then ‘6 = Yellow
Rows(iCntr).Delete

End If

Next
End Sub

I feel like I'm really close with the above code, but I keep getting a Compile error: End if without block if

[TMS]

It's this:

Then ‘6 = Yellow

The ‘ should be a ' (single quote)

Option Explicit

Sub sbDelete_Rows_Based_On_Cell_Color()

Dim lRow As Long
Dim iCntr As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'lRow = 24478
lRow = Range("A" & Rows.Count).End(xlUp).Row

For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1).Interior.ColorIndex = 6 Then ' 6 = Yellow
        Rows(iCntr).Delete
    End If
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

[RedditGold]

Thanks for the response, TMS. This code works on a small range of data (20 rows), but when I try to apply it to my larger range, it doesn't work. I run the code and nothing happens. Any ideas as to why?

Edit: I think I may have figured out why. The code works so long as there isn't and CF attached to it!

[TMS]

The code works on the fill colour, which is different to any CF colour. Filtering, if it worked, works with any cell colouring.

[RedditGold]

Yes, so now my question becomes how do I copy my data so that it retains the formatting so I can run the macro, but still doesn't bring the CF along with it. There's 100,000 rows of data...

It looks like you can copy tables into Word and then back into Excel without the CF, but Word locks up if it's pasting more than a handful of rows...

[TMS]

Did you use a formula in CF to colour the cells? If you did, maybe you could use that in a helper column?