Facing issues in deleting rows using Autofilter

Question

Sub DynamicRange()

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Sheet1")
Set StartCell = Range("A1")


  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column


  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
 ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:="Jan"`enter code here`
.Offset(1, 0).EntireRow.Delete
End Sub

I have tried to delete the rows but I am getting an error in the offset portion.

score 1 · Answer 1 · answered Apr 25 '21 at 19:51

You have not declared the object you are attempting to make the offset from. The "start with a dot" code should only be used within a With statement to determine the object. It's like walking into a fast-food restaurant, and telling the cashier "without ice", without telling them which drink you would like not to have ice with.

There are several ways to fix it. Here is a one I do not advise:

sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:="Jan"
Selection.Offset(1, 0).EntireRow.Delete

Here is a better version, which avoids using Select:

With sht.Range(StartCell, sht.Cells(LastRow, LastColumn))
    .AutoFilter Field:=1, Criteria1:="Jan"
    .Offset(1, 0).EntireRow.Delete
End With

Note that if you have any rows below the main data who do not a value in the StartCell column, then you will be deleting one of those with your offset. You can fix this by including a Resize:

.Offset(1, 0).Resize(.Rows.Count-1,).EntireRow.Delete

Facing issues in deleting rows using Autofilter

1 Answers1