Delete worksheet table based on date or blank date entry

[Adrain]

Hi

I would appreciate some help on the following problem, I have made a Excel table within a worksheet, wish to be able to allow the user to enter on a date, or leave a blank and delete the appropriate table row on the table when I ran the program it deleted row 10 of the worksheet, not the appropriate row on the table without affecting any other columns on worksheet.
Format that I am using is below followed by the program code that I was trying to achieve

Sub CellRowLoop()

Application.ScreenUpdating = False ' to make the program run faster
Dim lo As ListObject

idate = ActiveWorkbook.Worksheets("Sheet1").Range("d6") ' is the criteria on which the row is to be deleted

Set lo = Sheet1.ListObjects("tableentry")

For rw = lo.DataBodyRange.Rows.Count To 1 Step -1
If lo.DataBodyRange(rw, 3) = idate Then
lo.ListRows(rw).Delete
End If
Next

Application.ScreenUpdating = True

End Sub

 

[Alex Blakenburg]

I am a bit confused.
This lo.DataBodyRange(rw, 3) is column 3 "of the table", aren't you trying to match the date in Column 1 of the table ?

 

[Adrain]

I am a bit confused.
This lo.DataBodyRange(rw, 3) is column 3 "of the table", aren't you trying to match the date in Column 1 of the table ?

yes it is the 1st column of the table, my bad

 

[Alex Blakenburg]

What follows DataRange has a context of DataRange ? Did changing the 3 to a 1 fix the issue ?

 

[Adrain]

What follows DataRange has a context of DataRange ? Did changing the 3 to a 1 fix the issue ?

it did fix the issue thanks, but still do's not seem to delete the last record, even when i take out the step -1

 

[Alex Blakenburg]

Its actually more likely to be a data issue than a code issue but try this it should cover off some possible date issues.

Sub CellRowLoop()

Application.ScreenUpdating = False ' to make the program run faster
Dim lo As ListObject
Dim idate As Long
Dim rw As Long

idate = CLng(ActiveWorkbook.Worksheets("Sheet1").Range("d6").Value2) ' is the criteria on which the row is to be deleted

Set lo = Sheet1.ListObjects("tableentry")

For rw = lo.DataBodyRange.Rows.Count To 1 Step -1
    If CLng(lo.DataBodyRange(rw, 1).Value2) = idate Then
        lo.ListRows(rw).Delete
    End If
Next

Application.ScreenUpdating = True

End Sub