Macro works with data range but hangs when running on a Data Table RRS feed

  • Question

  • I use the code below to delete row not equal to a cell value. It worked great when running against a data range. When using it on a Data Table however, it hangs at the line 

    If Not rng Is Nothing Then rng.EntireRow.Delete

    Here is the entire code

    Sub Loop_Delete_Rows1()
        Dim Firstrow As Long
        Dim Lastrow As Long
        Dim Lrow As Long
        Dim CalcMode As Long
        Dim ViewMode As Long
        Dim rng As Range
        With ActiveSheet
            ViewMode = ActiveWindow.View
            ActiveWindow.View = xlNormalView
            'Set the first and last row to loop through
            Firstrow = .UsedRange.Offset(1).Row
            Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
            'Loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To Firstrow Step -1
                'Check the values in the A column
                With .Cells(Lrow, "A")
                    If Not IsError(.Value) Then
                        If .Value <> Worksheets("Variables").Range("C5") Then
                            If rng Is Nothing Then
                                Set rng = .Cells
                                Set rng = Application.Union(rng, .Cells)
                            End If
                        End If
                    End If
                End With
            Next Lrow
        End With
        'Delete all rows at once
        If Not rng Is Nothing Then rng.EntireRow.Delete
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = CalcMode
        End With
    End Sub

    Also, the watch window reads <Unable to get the Delete property of the Range class>Any input appreciated.

    • Edited by xtroublex Thursday, November 7, 2019 5:10 PM
    Thursday, November 7, 2019 4:32 PM

All replies

  • To: xtroublex
    re:  delete rows in a table

    A Table (not Data Table) in Excel is referred to as a ListObject when using VBA.
    The ListObject uses different rules regarding what parts are called and what you can do with the parts.
    A new "Table Tools" tab on the ribbon should appear when any cell is selected in the Table.
    The following code will delete rows from the table (ListObject) if a cell in the first column of the table equals the value in Cell "C5).
    (assumes the worksheet name is "Sheet1" and there is only one table on the worksheet)

    Sub DeleteSomeTableRows()
      Dim N As Long

      With ThisWorkbook.Worksheets("Sheet1").ListObjects(1)
        For N = .ListRows.Count To 1 Step -1
          If .DataBodyRange(N, 1).Value = ThisWorkbook.Worksheets("Sheet1").Range("C5").Value Then
          End If
        Next 'N
      End With

    End Sub

    Some New, some old Excel programs (now free) at MediaFire...
    Download from...

    Wednesday, November 13, 2019 2:02 AM