Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

Delete zeros sub

Hi,

I get the following error when i am trying to delete all rows in Col K that contain Zero

"Apllication-defined  or object-defined error"

It errors on line

(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete


Can anyone see why?

Thanks
Seamus
Rows("1:1").Select
    Range("H1").Activate
    Selection.AutoFilter
    Range("K1").Select
    Selection.AutoFilter Field:=11, Criteria1:="0"
  
   
   
    Worksheets("Suspense File").Range("K1").AutoFilter Field:=11, Criteria1:="0"
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
     ActiveSheet.ShowAllData

Open in new window

Avatar of jppinto
jppinto
Flag of Portugal image

Can you post a sample file please?
Avatar of Saqib Husain
Try

    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).entirerow.Delete
I tryed you code on a sample sheet and I didn't get any error so it would help to see a sample file please.
Avatar of Seamus2626

ASKER

Thanks ssaqibh but that never worked

Please find file attached Jppinto

Cheers,
Seamus
test.zip
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The macro is detecting "UsedRange" as the entiore sheet down to row 65536.  So when you try to use offset(1,0) you are referencing row 64437 which does not exist
Try

activesheet.autofilter.range.entirerow.delete

instead
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks guys,
Cheers,
Seamus