Clearing Multiple Sheet Range Selections

[Brutality]

Hi there,

I have the following code working but am wondering if is the safest approach. Obviously all it is doing is clearing the desired selection on the specified worksheets.

*****************

Sub ClearRunSheets()

Dim CodeNameArr, CodNam
Dim ws As Worksheet

CodeNameArr = Array("sShortRun", "sLongRun", "sMedRun", "sNARun")

With ActiveWorkbook
For Each CodNam In CodeNameArr
Set ws = .Worksheets(CStr(.VBProject.VBComponents(CodNam).Properties("Name")))
ws.Range("A2:Z1000") = ""
Next CodNam
End With

End Sub

*****************

I know I could use:

ws.Select
Range("A1:Z1000")
Selection.ClearContents

instead of the single line

ws.Range("A2:Z1000") = ""

to be sure, but setting the cells to empty hasn't given me any problems so far. Is there a reason not to use this approach?

TIA

/*Mark

 

[CWatts]

Why not just name the range and use a Range("RangeName").Clear command?

Probably wont matter on this scale, but cell by cell loop versus a single range is more efficient.

 

[Brutality]

True enough (there are actually only 7 sheets but I take the point re overhead and best practise). You do mean name the range in the spreadsheet, right? I know it can be done via code but for such a small case that seems more trouble than it's worth.

I just went with
wsh.Range("A2:Z1000").ClearContents

and am still learning the VB basics here.

 

[CWatts]

and am still learning the VB basics here.

Same. I know enough to awkwardly muddle through it so I'm always trying to pick things up myself.