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

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

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

Never mind I figured it out. Works great!
Thanks.

"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

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

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

This did work for me:

ActiveSheet.Range("A7:BW16,CA7:CV16," & _
"A18:DN18,A20:BA20,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:BA92," & _
"A97:BW106,CA97:CV106," & _
"A108:DN108,A110:BA110," & _
"A115:BW124,CA115:CV124").Value = 9

But I couldn't add too much more to that string of addresses. There is a limit
how long that string can be.

Since you're changing the values to 9, you could just split it up into multiple
statements:

with activesheet
.Range("A7:BW16,CA7:CV16,A18:DN18,A20:BA20").value = 9
.range("A25:BW34,CA25:CV34,A36:DN36,A38:BA38").val ue = 9
....
end with

Or you could extend Barb's suggestion using Union().

It looked like there was going to be a pattern to your range--if that's true,
then maybe you could use something like:

Sub testme01()
Dim iRow As Long

With ActiveSheet
For iRow = 7 To 115 Step 18
With .Cells(iRow, "A")
'get those big blocks
.Resize(10, 75).Value = 9
.Offset(0, 78).Resize(10, 22).Value = 9
'get the first lonely row
.Offset(11, 0).Resize(1, 118).Value = 9
'get the second lonely row
.Offset(13, 0).Resize(1, 53).Value = 9
End With
Next iRow
End With
End Sub

I don't know how close this is to your final range, though.


M.A.Tyler wrote:

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


--

Dave Peterson