Clearing Contents Efficiently

[ItalianPlatinum]

Hello - I am attempting to clear contents of cells efficiently. Below is what I have but I am erroring out on the highlighted section. giving me a runtime error. i dont want to select, or activate any worksheets. trying to speed things up as best i can. just trying to clear contents of all data in Cells A starting on A13, E13:K13 and L14:X14. then everything in my compare data sheet.

Sheets("Compare").Unprotect
With Sheets("Compare")
.Range("A13:A" & .Range("A" & rows.count).End(xlDown).row).ClearContents
End With

[B]With Sheets("Compare")
.Range("E13:K", .Range("E" & rows.count).End(xlDown).row).ClearContents
End With

With Sheets("Compare")
.Range("L14:X", .Range("L" & rows.count).End(xlDown).row).ClearContents
End With[/B]
        
With Sheets("Compare_DATA")
.Range("A:Z").ClearContents
End With

 

[Momentman]

Where do you get the runtime error? On what line

.Range("E13:K", .Range("E" & rows.count).End(xlDown).row).ClearContents

should be

.Range("E13:K" & .Range("E" & rows.count).End(xlDown).row).ClearContents

and repeat same for the L14:X range, that should clear the errors, i hope

 

[Joe4]

You have an error in your lines of code. Rows.Count counts the total number of possible rows on a sheet.
So if you start there, you cannot do down, only up.

Change all these references:
..." & rows.count).End(xlDown).row).ClearContents
to
..." & rows.count).End(xlUp).row).ClearContents

 

[Fluff]

How about

With Sheets("Compare")
   .Range("A13:A" & Rows.Count).ClearContents
   .Range("E13:K" & Rows.Count).ClearContents
   .Range("L14:X" & Rows.Count).ClearContents
End With

 

[ItalianPlatinum]

How about

With Sheets("Compare")
   .Range("A13:A" & Rows.Count).ClearContents
   .Range("E13:K" & Rows.Count).ClearContents
   .Range("L14:X" & Rows.Count).ClearContents
End With

appears to be working. Thanks! always appreciated

 

[Fluff]

Glad we could help & thanks for the feedback.