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
