Delete row if text is blue

richn

Board Regular
Joined
Nov 21, 2002
Messages
70
I have a spreadsheet with about a thousand rows. 250 of the rows appear in blue text. These are entries I don't need. I'd like to be able to delete these without ctrl clicking each individual one! I kind of have an idea of how to do (logically) it but don't know the syntax - if anyone could help that would be great.

What I think I need to do is: if A1=blue then place a 1 in H1. If A1 is any other colour then place a 0 in H1. I can then sort by column H1 and delete all the 1's.

Any help?

Cheers

Rich
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
A quick and simple little macro would do the trick.
Code:
Sub KillTheBlues()
    Dim lngBottom As Long, r As Long
    
    ' Change the "A" to an appropriate column reference
    lngBottom = Range("A65536").End(xlUp).Row
    
    For r = lngBottom To 1 Step -1
        
        ' Change the 1 (column A) to the number of the
        ' column that would have the blue text.
        If Cells(r, 1).Font.ColorIndex = 5 Then    ' blue
            Cells(r, 1).EntireRow.Delete
        End If
    Next r
End Sub
HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,123,315
Messages
5,600,906
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top