Clear Cells on multiple sheets in Workbook - VBA not working!

[robbertly]

Hello,

A quick question I am wondering if somebody can answer. I have a range of workbooks each with 24 Sheets.

I want to delete a specific set of cells with in each worksheet within each workbook - A10015 to AC10254

I have tried two options - but neither seems to work on all sheets in the workbook:

Sub Deleteafter9999()
'
' Deleteafter9999 Macro
' delete after 9999
'

'
Dim ws As Worksheet

For Each ws In Worksheets
With ws.Sort
Rows("10015:10255").Select
Selection.ClearContents
End With
Next ws
End Sub

and also

Sub stone()
Dim a As Long
For a = 1 To Sheets.Count
Worksheets(a).Range("A210015:AC10255").ClearContents
Next a
MsgBox "Complete"
End Sub

Any comments / suggestions welcome.

Thanks

 

[alansidman]

You indicate that you want to delete the rows, however your code is only removing the contents of the cells in those rows. Please clarify what you want to do. Clear the contents or delete the rows? If delete then change the line of code Selection.ClearContents to Selection.Entirerow.delete or in your second example it should read Worksheets(a).Range("A210015:AC10255").delete

 

[robbertly]

Hello Alan,

Thanks for the reply and apologies for any confusion.

Ideally I would like to clear the contents of rows A210015:AC10255 on all worksheets in the workbook using VBA. I was using those examples I found online in a attempt to get something to work.

Which of those examples above would best do this and would I need to make any additional changes apart from the ones in your reply?

Which is technically the better / best to use?

Thanks,

 

[alansidman]

try this instead

Option Explicit

Sub Foo()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Rows("10015:10225").ClearContents
    Next ws
End Sub

 

[robbertly]

Hello Alan,

Thanks for following up so quickly.

Thats seems to have worked a treat..... thanks very much for the help.

Much appreciated, keep up the good work (for newbies like me!)

Thanks