VBA to Delete Table rows based on value in first column

can you upload an example file?

For introw = 9999 To 1 Step -1
    If Cells(introw, 1) = 43 Then
        Rows(introw).Delete
    End If
Next

Select allOpen in new window

That goes through 9999 rows, and deletes any rows in which the value of column A is 43.

you can put this in sheet object code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim i As Long
Dim LR As Long

Application.ScreenUpdating = False

LR = Cells(Rows.Count, 1).End(xlUp).Row

For i = LR To 2 Step -1

    If Range("A" & i) = "Your Text" Then
        Range("A" & i).EntireRow.Delete Shift:=xlUp
    End If
    
Next i

Application.ScreenUpdating = True
   

End Sub

Select allOpen in new window

see the = "Your Text"
change it as per your need.  if it is a number then remove the " " double quote and just put the number

if you do not want that the row is deleted everytime a condtion is made.

then you can do it through simple running macro . by putting the code into the module and then run it there.

Sub Macro
Dim i As Long
Dim LR As Long

Application.ScreenUpdating = False

LR = Cells(Rows.Count, 1).End(xlUp).Row

For i = LR To 2 Step -1

    If Range("A" & i) = "Your Text" Then
        Range("A" & i).EntireRow.Delete Shift:=xlUp
    End If
    
Next i

Application.ScreenUpdating = True

End Sub

Select allOpen in new window

How about just using the Filter option on the table?

Filter column 1 to show only the values that you want to delete, outside of the table select the rows that are left visible and Delete Rows using "Ctrl & -" or the menu option on the Home Tab, Cells Group. This has to be done outside of the table because doing it within the table can cause strange results.

Disable Filter and those values that you wanted to keep will still be there.

Thanks
Rob H

I just want to delete the rows in the table not the entire sheet row.
Can't I define the range as the table, cycle through the rows and check the value in the first column?

yes you can

what is the code for this?

I assume there is other data alongside the table that you need to keep? If not then it doesn't matter that you are deleting the whole row.

Thanks
Rob H

Correct.  I just thought there may be a simple reference to table cells that I could use rather than defining the range over again.

here is the code you need

Sub DeleteRowsSecondFastest()
''''''''''''''''''''''''''
'Written by www.ozgrid.com
''''''''''''''''''''''''''
Dim rTable As Range
Dim rCol As Range, rCell As Range
Dim lCol As Long
Dim xlCalc As XlCalculation
Dim vCriteria

On Error Resume Next
   'Determine the table range
     With Selection
         If .Cells.Count > 1 Then
             Set rTable = Selection
         Else
             Set rTable = .CurrentRegion
             On Error GoTo 0
         End If
    End With
   
    'Determine if table range is valid

    If rTable Is Nothing Or rTable.Cells.Count = 1 Or WorksheetFunction.CountA(rTable) < 2 Then
        MsgBox "Could not determine you table range.", vbCritical, "Ozgrid.com"
        Exit Sub
    End If

    'Get the criteria in the form of text or number.

    vCriteria = Application.InputBox(Prompt:="Type in the criteria that matching rows should be deleted. " _
    & "If the criteria is in a cell, point to the cell with your mouse pointer", _
    Title:="CONDITIONAL ROW DELETION CRITERIA", Type:=1 + 2)

    'Go no further if they Cancel.

    If vCriteria = "False" Then Exit Sub

    'Get the relative column number where the criteria should be found

    lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _
    & "the criteria can be found.", Title:="CONDITIONAL ROW DELETION COLUMN NUMBER", Type:=1)

    'Cancelled
    If lCol = 0 Then Exit Sub
        'Set rCol to the column where criteria should be found
        Set rCol = rTable.Columns(lCol)
        'Set rCell to the first data cell in rCol
        Set rCell = rCol.Cells(2, 1)

    'Store current Calculation then switch to manual.
    'Turn off events and screen updating
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

   

  'Loop and delete as many times as vCriteria exists in rCol
   For lCol = 1 To WorksheetFunction.CountIf(rCol, vCriteria)
        Set rCell = rCol.Find(What:=vCriteria, After:=rCell, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(-1, 0)
            rCell.Offset(1, 0).EntireRow.Delete
   Next lCol

   
    With Application
        .Calculation = xlCalc
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   On Error GoTo 0
   
   

End Sub

Select allOpen in new window

click on any cell on the given table that you want to have the macro run.

then it will ask you that enter the criteria the condition based on which you want to trigger the deletion,

then it will ask you the column in which the value need to be looked in.  in your case if it is column A then type 1 and if it is column B type 2

then click ok and it will do the job

You should be able to use

Selection.ListObject.ListRows(1).Delete

Thanks
Rob H

rCell.Offset(1, 0).EntireRow.Delete

I don't want to delete the entire worksheet row just the table row

Thanks, that did the trick