Delete row values in table if cell value = X

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,832
Office Version
  1. 2007
Platform
  1. Windows
On my worksheet i have a table of values.
Row 3 are headers then values are down the page from there.
Table range values are column N to S

I am using the code below.
What i am looking to do is if the value in the table column S = X then delete the row values for column N to S where X is currently shown.
X may be on one row or many rows.

See example screenshot supplied, Column S value is X so the code needs to delete that rows values.

With the code supplied i get a RTE 1004 "OPERATION NOT ALLOWED, THE OPERATION IS ATTEMPTING TO SHIFT CELLS IN A TABLE"

Rich (BB code):
Private Sub DeleteCustomer_Click()
    Dim tblName As String
    Dim tbl As ListObject
    Dim R As Long
    Dim lr As Long
    Dim i As Long
    
    If ActiveCell.Column = 19 And ActiveCell.Row > 3 And ActiveCell.Value = "X" Then
       ActiveCell.Resize(1, 6).Delete
           
    End If

End Sub
 

Attachments

  • EaseUS_2023_05_12_09_15_38.jpg
    EaseUS_2023_05_12_09_15_38.jpg
    12.5 KB · Views: 16

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I did this as a test and it deletes the entire row.
So how do i edit it so the code only delete the row in the table of which is column N to S

Rich (BB code):
Private Sub DeleteCustomer_Click()
    Dim tblName As String
    Dim tbl As ListObject
    Dim R As Long
    Dim lr As Long
    Dim i As Long
    
    If ActiveCell.Column = 19 And ActiveCell.Row > 3 And ActiveCell.Value = ("X") Then
       ActiveCell.EntireRow.Delete
    End If

End Sub
 
Upvote 0
I don't work much with tables, but we can incorporate the method shown in the first response here: How to delete the active table row in a ListObject?

So, your final code would look like:
VBA Code:
Private Sub DeleteCustomer_Click()
    Dim tblName As String
    Dim tbl As ListObject
    Dim R As Long
    Dim lr As Long
    Dim i As Long
    Dim ActiveTableRow As Long
    
    If ActiveCell.Column = 19 And ActiveCell.Row > 3 And ActiveCell.Value = "X" Then
        ActiveTableRow = Selection.Row - Selection.ListObject.Range.Row
        Selection.ListObject.ListRows(ActiveTableRow).Delete
    End If

End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,224,828
Messages
6,181,214
Members
453,024
Latest member
Wingit77

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