If table cell equals variable value

Question

I've found plenty of help for deleting a row if a cell equals a number specified in the code, but I'm having trouble deleting a table row if the value of a cell equals the value of a variable.

(This is my first ever attempt at VBA and first ever use of a site like this to seek help on any subject. I appreciate any help you can lend.)

Sub NeverGonnaWork

Dim x as String

x = Sheets("Sheet1").Range("B5").Value

Dim tbl as ListObject

Set tbl = Sheets("Sheet2").ListObjects("Table2")

'if column 15 in last row of the table equals x, delete that row of the table'

If Cells(tbl.ListRows.Count, (15)) = x then
tbl.ListRows(tbl.ListRows.Count).Delete

End If

End Sub

Answer 1

It "doesn't work", because you are referencing Worksheet.Cells(<amount of rows in table>, 15) rather than the actual cell position inside the table.

In other words, you're just referencing some "random" cell in your excel Worksheet.

Change your code to this and it should work ;-)

Private Sub thisWillWork()

  Dim compVal as String: compVal = Sheets("Sheet1").Range("B5").Value2
  Dim tbl as ListObject: Set tbl = Sheets("Sheet2").ListObjects("Table2")

  If tbl.ListRows(tbl.ListRows.Count).Range(,15) = compVal Then
     tbl.ListRows(tbl.ListRows.Count).Delete
  End If

End Sub