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

Seamus2626Asked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
The reason for the error is that the usedrange covers the entire sheet ie 65536 rows. And while trying to offset the usedrange by 1,0 it is pushed out of the sheet which gives the error.
0
 
jppintoCommented:
Can you post a sample file please?
0
 
Saqib Husain, SyedEngineerCommented:
Try

    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).entirerow.Delete
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
jppintoCommented:
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.
0
 
Seamus2626Author Commented:
Thanks ssaqibh but that never worked

Please find file attached Jppinto

Cheers,
Seamus
test.zip
0
 
Rory ArchibaldCommented:
Change line 11 to:
ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Rows.Count - 1).Offset(1, 0).Rows.Delete

Open in new window

as the Usedrange is the whole sheet.
0
 
aflockhartCommented:
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
0
 
Saqib Husain, SyedEngineerCommented:
Try

activesheet.autofilter.range.entirerow.delete

instead
0
 
aflockhartCommented:
But there are other problems with your approach as well.  If you try running it on a sheet that has NO zero entries in column K, you may find that it deletes *all* the data. That's what happened in my test sheet.  All the rows that it selects are hidden, and in this case the delete action seems to allow the hidden rows to be deleted.
0
 
Seamus2626Author Commented:
Thanks guys,
Cheers,
Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.