[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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

0
Seamus2626
Asked:
Seamus2626
  • 3
  • 2
  • 2
  • +2
3 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Seamus2626Author Commented:
Thanks ssaqibh but that never worked

Please find file attached Jppinto

Cheers,
Seamus
test.zip
0
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now