that worked well too, is there a limit as to how many ranges I can add? Seems
like I'm incuring a syntax error much after 26 or so.

ActiveSheet.Range("A7:BW16,CA7:CV16,A18:DN18,A20:B A20,A25:BW34,CA25:CV34,A36:DN36,A38:BA38,A43:BW52, CA43:CV52,A54:DN54,A56:BA56,A61:BW70,CA61:CV70,A72 :DN72,A74:BA74,A79:BW88,CA79:CV88,A90:DN90,A92:BA9 2,A97:BW106,CA97:CV106,A108:DN108,A110:BA110,A115: BW124,CA115:CV124").Value = 9

Next would be A126:DN126,A128:BA128
when I add, I get Run-time error '1004': Application-defined or
object-defined error?

Any thoughts?

"Dave Peterson" wrote:

Barb's code needs a space before that underscore character.

Sub ClearRange()
dim myrange as range

set myrange = union(range("A7:BW16"),range("CA7:CV16"), range("A18:DN18"), _
range("A20:BA20"))

myrange.value = 9

end sub

Alternatively, you could use:

Option Explicit
Sub ClearRange2()
Activesheet.Range("A7:BW16,CA7:CV16,A18:DN18,A20:B A20").Value = 9
end Sub

M.A.Tyler wrote:

When I debug I get a compile syntax error? Am I doing something wrong?

"Barb Reinhardt" wrote:

I'm assuming you want to clear all of the ranges listed

Sub ClearRange()
dim myrange as range

set myrange = union(range("A7:BW16"),range("CA7:CV16"), range("A18:DN18"),_
range("A20:BA20"))

myrange.value = 9

end sub

HTH,
Barb Reinhardt

"M.A.Tyler" wrote:

I need to write a macro that clears several fields and replaces the empty
cells with 9. The fields are as follows A7:BW16, CA7:CV16, A18:DN18 AND
A20:BA20

Thanks as always!

M.A.Tyler

--

Dave Peterson