Run-time error '1004' This won't work because it would move cells in a table on your worksheet.
I'm trying to run a loop to delete Table Rows (not Sheet Rows) that meet a certain condition.
It's working up to a point, and I'm not sure why. Can someone take a look at this and tell me where I'm going wrong? It would be greatly appreciated. Thank you,
Here's what the Table looks like before running the code:
This is what the Table looks like when it stops working:
Here's my VBA code:
I'm trying to run a loop to delete Table Rows (not Sheet Rows) that meet a certain condition.
It's working up to a point, and I'm not sure why. Can someone take a look at this and tell me where I'm going wrong? It would be greatly appreciated. Thank you,
Here's what the Table looks like before running the code:
VBA Testing.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | ID | Author | Series | Preferred | |||
2 | 100 | Dr. Seuss | Berenstain Bears | Berenstain | |||
3 | 101 | Larrison, Joanne | Corduroy | ||||
4 | 102 | Lucado, Max | Lucado, Max | ||||
5 | 103 | Mayer, Mercer | Corduroy | Seuss | |||
6 | 104 | Rey, Margret | Zonderkidz | Zonderkidz | |||
7 | 105 | Willems, Mo | |||||
DEL Rows |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
L2:M3,B2:C7 | Expression | =SUM(COUNTIF(B2,"*"&lstPreferred&"*")) | text | NO |
This is what the Table looks like when it stops working:
Here's my VBA code:
VBA Code:
' Delete Table Rows based on Cell Interior Color (B:C)
Sub DeleteTableRowsByColor()
Dim tbl As ListObject
Dim LastRow As Long, xRow As Long
xRow = 2
Set tbl = ActiveSheet.ListObjects("tblBooks2A")
LastRow = tbl.Range.Rows.Count 'Counts all Rows in Table Range
Do Until xRow = LastRow + 1
If Cells(xRow, 3).DisplayFormat.Interior.Color = RGB(255, 199, 206) Then
Cells(xRow, 3).Select
Selection.Rows.Delete
xRow = xRow - 1
LastRow = LastRow - 1
End If
xRow = xRow + 1
Loop
End Sub