Share via

Deleting Certain Rows from Tables using VBA - ERROR

Anonymous
2017-03-26T23:40:37+00:00

Hello,

I built a sub that takes in two criteria: deviceName and deviceType. If these two match a row in my tables, that row will be deleted. For example, if I specify deviceName = "Pressure Sensors" and deviceType = "MEMS", the sub will go through every table in my sheet and delete the rows where the criteria match:

However, the problem comes when I have tables (one below another).  Before, when I first wrote the sub, I had the 2 tables side by side and everything worked fine. However, I am experimenting with a tester sheet. The actual sheet I am about to build will have over 20 tables, one below the other and some side by side. So I decided to test this code by putting the tables below one another and seeing what happens. 

From the picture above, you can see that I am getting an error that reads:"Run-time error '1004': Application-defined or object-defined error."

Using Excel VBA, is there a way to DELETE specific rows from tables (with one below another) without getting this error? Before I got great help from the forums and was able to get a sub for inserting the values into these tables (resizing the tables and adding a row below). However, with delete I can't use similar code since this deals with SPECIFIC rows to delete (could be in the middle, at the top, the bottom, etc.) 

Here is a small snippet of my code, trying to delete values:

' Sub-function used to delete values from table

' Source: http://stackoverflow.com/questions/29583637/how-to-delete-rows-in-an-excel-listobject-based-on-criteria-using-vba

Private Sub deleteTblRows(tbl As ListObject, column1 As String, column2 As String, criteria1 As String, criteria2 As String)

    Dim i As Long, lastRow As Long, temp As ListRow

    ' lastRow contains total rows of table

    lastRow = tbl.ListRows.Count

    ' Starting from bottom, iterate to the top

    For i = lastRow To 1 Step -1

        ' temp will contain the current row

        Set temp = tbl.ListRows(i)

        ' If the current row has criteria1 (deviceName) that matches a value in column1 ("Device") AND

        ' criteria2 (deviceType) that matches a value in column2 ("DeviceType"), delete that row

        If Intersect(temp.Range, tbl.ListColumns(column1).Range).Value = criteria1 And Intersect(temp.Range, tbl.ListColumns(column2).Range).Value = criteria2 Then

            tbl.ListRows(1).Delete ' ************** ERROR HERE

        End If

    Next i

End Sub

Please let me know if anybody has ideas! Anything is much appreciated!!

Thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. OssieMac 48,001 Reputation points Volunteer Moderator
    2017-04-04T04:20:14+00:00

    Example workbook uploaded to following link.

    https://1drv.ms/u/s!ArAXPS2RpafCjjt5nIQCiC0bp-vv

    In Private Sub deleteTblRows you will see the first section of code that I added between asterisk lines and the second section between hash lines.

    I added Sub ResizeTblCols which is between ampersand lines.

    At the top of Module 1 you will see the following Constant declared. Note the comment that it must be at top of a standard Module.

    Public Const lngTempCols As Long = 3

    • The value for the constant must be the largest number of columns in any of the tables that are created vertically.
    • When creating more tables vertically then the left column of all tables must be the same column.
    • If adding any tables horizontally then leave at least one blank column between the widest tables.
    • If you add any tables with more that 3 columns then increment the value of the constant to match.
    • The same Sub ResizeTblCols is called to increment the number of columns and to later decrement the number of columns.
    • When it is first called to increment the number of columns, it uses the declared constant for the number of columns as the parameter for columns.
    • When it is called to dwonsize again, it uses the saved variable for the initial number of columns as the columns parameter.
    • The loop at the end of Private Sub deleteTblRows removes superfluous formatting that gets left behind when the table is downsized.

    I have extensively commented the code to assist you to understand it but feel free to get back to me if you require further explanation.

    0 comments

12 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-03-27T23:22:40+00:00

    Hi OssieMac,

    Thank you for the code. I ran it and double checked to the arguments. The referencing seems to be fine, locations are all fine (even checked the Msgbox)... Yet I'm still getting the same error. 

    But thanks for the help!

    0 comments
  2. OssieMac 48,001 Reputation points Volunteer Moderator
    2017-03-27T01:49:24+00:00

    In  my simple testing as per the code below, there does not appear to be anything wrong with the code as such.

    The error message suggests that it is attempting to reference something on an incorrect worksheet or location in the workbook. Therefore, ensure that the code that calls the sub passes the correct information via the arguments (or parameters) to the called sub as per the example code below.

    Create a table in a blank workbook and run Test. You will see that I have included a MsgBox to demonstrate that when the ListObject is properly referenced before passing to the called sub, it contains all information about the table.

    Sub Test()

        Dim ws As Worksheet

        Dim lstObj As ListObject

        Set ws = Worksheets("Sheet1")

        Set lstObj = ws.ListObjects("Table1")

        Call deleteTblRows(lstObj)

    End Sub

    Private Sub deleteTblRows(tbl As ListObject)

        Dim i As Long, lastRow As Long, temp As ListRow

        'Following MsgBox to demonstrate that the ListObject

        'contains all information (Table Name, Workbook, Worksheet and Table Range)

        MsgBox tbl.Name & " " & tbl.Range.Address(External:=True)

        lastRow = tbl.ListRows.Count

        For i = lastRow To 1 Step -1

            Set temp = tbl.ListRows(i)

            tbl.ListRows(i).Delete

            'temp.Delete  'Alternative to previous row

        Next i

     End Sub

    0 comments
  3. Anonymous
    2017-03-27T00:31:14+00:00

    Hi OssieMac,

    Yes the code fails on the first loop. After you pointed that out, I tried deleting that specific row by setting tbl.ListRows(i).Delete and I am still getting the same error, failing at the first iteration.

    Not sure if this is a resizing problem, where the table needs to be resized before or after deleting? I had that error when I was building my insert sub.

    0 comments
  4. OssieMac 48,001 Reputation points Volunteer Moderator
    2017-03-27T00:23:23+00:00

    Does the code fail on the first loop or maybe the last loop. Your code deletes Row 1 (Row one) instead of the row represented by the variable i. I believe that a table must have at least one data row below the column headers so is it deleting all rows because of the criteria match and then attempting to delete the last remaining row of the table.

    0 comments