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

zero269

Active Member
Joined
Jan 16, 2023
Messages
253
Office Version
  1. 365
Platform
  1. Windows
Run-time error '1004' This won't work because it would move cells in a table on your worksheet.

1676781716481.png

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
ABCDE
1IDAuthorSeriesPreferred
2100Dr. SeussBerenstain BearsBerenstain
3101Larrison, JoanneCorduroy
4102Lucado, MaxLucado, Max
5103Mayer, MercerCorduroySeuss
6104Rey, MargretZonderkidzZonderkidz
7105Willems, Mo
DEL Rows
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L2:M3,B2:C7Expression=SUM(COUNTIF(B2,"*"&lstPreferred&"*"))textNO


This is what the Table looks like when it stops working:
1676781807411.png

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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Have you looked Here?

I think you will find a circular reference to this thread.
 
Upvote 0
Hi zero269,

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

VBA Code:
' 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
 
Upvote 1
Solution
Try...

VBA Code:
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!
 
Upvote 1
Hi zero269,

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

VBA Code:
' 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.
 
Upvote 0
Try...

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top