Deleting rows in the middle of a table

[NWA John]

I have a table 5 columns by 20 rows. In the first column "A" is colors: green, blue, orange, red & yellow. I have sorted the range A1:E20 by column 'A" colors and need to delete the rows with "red" in column A.

Just having a brain fart... Thanks for your help!

[Aussiebear]

You could try this. Just remember to change shData to the name of your sheet.


Sub Delete_Rows_Based_On_Color()
Dim lRow As Long
Dim iCntr As Long
lRow = shData.Cells(shData.Rows.Count,1).End(xlUp).Row
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 1).Interior.ColorIndex = 3 Then '3 = Red
Rows(iCntr).Delete
End If
Next
End Sub

[georgiboy]

Another option if you have values around the table and do not want to delete whole rows from the worksheet, it will not touch any cells on the worksheet that are not part of the table object:

Sub test()
Dim tbl As ListObject

Set tbl = Sheet1.ListObjects("Table1") '< change to suit

Application.DisplayAlerts = False
With tbl
.Range.AutoFilter 1, RGB(0, 176, 80), xlFilterCellColor
If Application.CountA(.ListColumns(1).Range.SpecialCells(xlCellTypeVisible)) > 1 Then
.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
End If
.Range.AutoFilter 1
End With
Application.DisplayAlerts = True
End Sub

NOTE: Edit the RGB numbers to the colour you need, i have used a standard Excel green

[arnelgp]

You only have 20 rows, so what is "hard" on manually deleting the rows (red).
click and drag the "row" indicator of all red and use Ctrl - (ctrl + negative symbol) to delete the entire rows.

[Bob Phillips]

Is it a listobject table? If so, just click the down arrow at the head of column A, select red only, then delete the visible rows.

[NWA John]

Thanks all for the help! Apologies for the late response on my part