MrExcel Publishing
Your One Stop for Excel Tips & Solutions

deleting highlighted cells


Posted by Moomal Abro on July 03, 2001 3:11 AM

I have a file, in which I have writting some VBA to highlight certain cells. I now need to delete all the records that do not contain highlighted cells. In other words:

In column A, I have used VBA to highlight certain cells. There is other information in other columns, but only column A has highlighted information. I now need to go through the records and, in column A, find the cells that are not highlighted, and delete the entire row containing an unhighlighted A cell.

I'm sure this must be very easy to do, but unfortunately my knowledge of VBA is extremely limited.


Posted by Ben O. on July 03, 2001 7:51 AM

This code should work. You'll have to change the 100 in "rEnd = 100" to the last row you want to check. You can also change the 1 in "rBegin = 1" if you want to start checking at a later row.

Sub RowDelete()
Application.ScreenUpdating = False

Dim myRow As Integer
Dim myCol As Integer
Dim Counter As Integer

Counter = 0
myCol = 1
rBegin = 1
rEnd = 100

For myRow = rEnd To rBegin Step -1
Application.StatusBar = Counter & " rows deleted."
If Cells(myRow, myCol).Interior.ColorIndex = xlNone Then
Cells(myRow, myCol).EntireRow.Delete
Counter = Counter + 1
End If
Next myRow
Application.StatusBar = False
Application.ScreenUpdating = True
x = MsgBox(Counter & " rows deleted.", vbOKOnly, "Rows Deleted")

End Sub

-Ben