VBA: Run-time error '1004' Trying to Delete "Table" Rows

[zero269]

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:

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:

' 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

 

[johnnyL]

Have you looked Here?

I think you will find a circular reference to this thread.

 

[HaHoBe]

Hi zero269,

when deleting rows you should always start at the bottom and work tp the top to avoid manipulating the counter.

' Delete Table Rows based on Cell Interior Color (B:C)
Sub DeleteTableRowsByColor()

  Dim objLO As ListObject
  Dim lngRow As Long
  
  Set objLO = ActiveSheet.ListObjects("tblBooks2A")
  
  lngRow = objLO.Range.Rows.Count 'Counts all Rows in Table Range
  
  Do Until lngRow = 1
    If Cells(lngRow, 3).DisplayFormat.Interior.Color = RGB(255, 199, 206) Then
      objLO.Range.Rows(lngRow).Delete
    End If
    lngRow = lngRow - 1
  Loop

End Sub

Ciao,
Holger

 

[Domenic]

Try...

Sub DeleteTableRowsByColor()

    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("tblBooks2A")
    
    Dim rw As Long
    With tbl.ListRows
        For rw = .Count To 1 Step -1
            With .Item(rw)
                If .Range(1, 2).DisplayFormat.Interior.Color = RGB(255, 199, 206) Or _
                    .Range(1, 3).DisplayFormat.Interior.Color = RGB(255, 199, 206) Then
                        .Delete
                End If
            End With
        Next rw
    End With

End Sub

Hope this helps!

 

[zero269]

Hi zero269,

when deleting rows you should always start at the bottom and work tp the top to avoid manipulating the counter.

' Delete Table Rows based on Cell Interior Color (B:C)
Sub DeleteTableRowsByColor()

  Dim objLO As ListObject
  Dim lngRow As Long
 
  Set objLO = ActiveSheet.ListObjects("tblBooks2A")
 
  lngRow = objLO.Range.Rows.Count 'Counts all Rows in Table Range
 
  Do Until lngRow = 1
    If Cells(lngRow, 3).DisplayFormat.Interior.Color = RGB(255, 199, 206) Then
      objLO.Range.Rows(lngRow).Delete
    End If
    lngRow = lngRow - 1
  Loop

End Sub

Ciao,
Holger

Hi Holger,

Thanks for looking into this for me. You're right. I should definitely be deleting from the bottom up. I built that code based on a YT series on VBA and it was the closest I could get to actually deleting Table Rows based on the RGB value for a conditionally formatted cell.

It's a great video series, but a lot to grasp. After looking at your code, I can see where I was way over my head.

I tested your code, and it ran without any errors and only deleted the cells in Column C that were conditionally formatted with the defined RGB values.
Thank you so much for this. It's very much appreciated.

 

[zero269]

Try...

Sub DeleteTableRowsByColor()

    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("tblBooks2A")
   
    Dim rw As Long
    With tbl.ListRows
        For rw = .Count To 1 Step -1
            With .Item(rw)
                If .Range(1, 2).DisplayFormat.Interior.Color = RGB(255, 199, 206) Or _
                    .Range(1, 3).DisplayFormat.Interior.Color = RGB(255, 199, 206) Then
                        .Delete
                End If
            End With
        Next rw
    End With

End Sub

Hope this helps!

Hi Domenic,

This is a great solution. Love the OR covering both columns. Ultimately, I was just looking to get one column done and then somehow make it work for both... but you've done that already and it's perfect for my use case as I do have two columns that need to be checked.

Thank you so much... this is definitely a SOLUTION as well.