CODEWITHSUNDEEP
vbaexcel
Anuj
Anuj

Reputation: 107

Delete method of range class failed after filtering table

I am trying to delete all the visible rows in the table after autofilter but it is giving me that error. I tried looking up at various places since this question is asked before but I couldn't find any solution till now here's the code

Set rng = wk.Range(wk.Range("A1"), wk.Range("A1").SpecialCells(xlLastCell))
        Set tbl = wk.ListObjects.Add(xlSrcRange, rng, , xlYes)

        tbl.Range.AutoFilter Field:=16
        tbl.Range.AutoFilter Field:=16, Criteria1:="=NR", Operator:=xlOr, Criteria2:="="

This line results in error : wk.Range("$A$1:$AB$" & frow).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

        tbl.Range.AutoFilter Field:=16

I also tried this sub replacing the errored line but it gave me same error when deleting the rows 

'Call RemoveVisibleRows(wk)

This is the subroutine for the same

Sub RemoveVisibleRows(wDel As Worksheet)

Dim oRow As Range, Drng As Range
Dim myRows As Range

With wDel
    Set myRows = Intersect(.Range("A:A").EntireRow, .UsedRange)
    If myRows Is Nothing Then Exit Sub
End With

For Each oRow In myRows.Columns(1).Cells
    If Not oRow.EntireRow.Hidden And oRow.Row <> 1 Then
        If Drng Is Nothing Then
            Set Drng = oRow
        Else
            Set Drng = Union(Drng, oRow)
        End If
    End If
Next

Error Line : If Not Drng Is Nothing Then Drng.EntireRow.Delete

End Sub

Upvotes: 2

Views: 1098

Answers (1)

user6432984
user6432984

Reputation: 

On Error Resume Next
Set Target = wk.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not Target Is Nothing Then
    If Target.Rows > 1 Then
        Target.Offset(1).Resize(Target.Rows.Count - 1).EntireRow.Delete
    End If
End If

Upvotes: 1

Related Questions