Delete Filtered rows without headers

HomePro

New Member
Joined
Aug 3, 2021
Messages
20
Office Version
  1. 2019
Platform
  1. Windows
I am using VBA code to run a filter, copy the results to a new sheet and then delete the results from the original sheet but not the header.
Then I am running a different filter and repeating that. But when I try to delete those results I get the error
Application-defined or object-defined error. I do not know what this means or how to get around it. Can anyone help?

VBA Code:
Sub WORKING()
'PARSE AGREEMENT TYPE
  'GET LAST ROW
    lR = Cells(Rows.Count, 1).End(xlUp).Row
    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("A1:L" & lR).AutoFilter field:=4, Criteria1:= _
        "= AGREEMENT TYPE", Operator:= _
        xlOr, Criteria2:="="
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Choose Agreement"
    ActiveSheet.Paste
    Sheets("DATA").Select
'DELETE FILTERD ROWS
    ActiveSheet.AutoFilter.Range.Offset(1).Delete xlShiftUp
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
'PARSE CALL BACKS BY CSR
  'Kayla
   'GET LAST ROW
    lR = Cells(Rows.Count, 1).End(xlUp).Row
    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("A1:L" & lR).AutoFilter field:=2, Criteria1:= _
    "Kayla", Operator:=xlOr, Criteria2:="="
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Kayla"
    ActiveSheet.Paste
    Sheets("DATA").Select
'DELETE FILTERD ROWS
   MsgBox ActiveSheet.Name
   Active.Sheet.AutoFilter.Range.Offset(1).Delete xlShiftUp
   Selection.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
   If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
As Active.Sheet does not exist …​
 

HomePro

New Member
Joined
Aug 3, 2021
Messages
20
Office Version
  1. 2019
Platform
  1. Windows
I added " MsgBox ActiveSheet.Name" to confirm it did nd it does return the correct sheet.

But as per your suggestion I changed it to Sheets("DATA").AutoFilter.Range.Offset(1).Delete xlShiftUp and the error persist.
Any suggestion on how to get around this?
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows

Just remove first the dot between Active & Sheet …​
 

Forum statistics

Threads
1,148,257
Messages
5,745,700
Members
423,969
Latest member
CHHeights

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top