none
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
    
        
            .Select
    
            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
                            Else
                                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
             .ListRows(N).Delete
          End If
        Next 'N
      End With

    End Sub
    '---


    Some New, some old Excel programs (now free) at MediaFire...
    Download from...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

       
    Wednesday, November 13, 2019 2:02 AM