I need a macro that will basically zero out all cells in three different
ranges. i cant get it to work.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200702/1

Hi,

You could use something along the lines of:

Sub ClearIt()
Dim i As Integer
Dim RngArry As Variant
RngArry = Array("A1:J10", "K11:T20", "U21:AD30")
If LBound(RngArry) < UBound(RngArry) Then
For i = LBound(RngArry) To UBound(RngArry)
ActiveSheet.Range(RngArry(i)).Clear
Next
End If
End Sub

This clears the cells in the defined ranges. Replace the ranges in Array("A1:J10", "K11:T20", "U21:AD30") with your own. You can
add/delete ranges as you like.

Cheers

--
macropod
[MVP - Microsoft Word]


"brownti via OfficeKB.com" <u31540@uwe wrote in message news:6de8590bc0d23@uwe...
| I need a macro that will basically zero out all cells in three different
| ranges. i cant get it to work.
|
| --
| Message posted via OfficeKB.com
| http://www.officekb.com/Uwe/Forums.a...excel/200702/1
|

This works for me, but it changes all of my formating, is there a way to
instead of clearing the cells, to just enter a zero instead of the number
that is in the cell?


macropod wrote:
Hi,

You could use something along the lines of:

Sub ClearIt()
Dim i As Integer
Dim RngArry As Variant
RngArry = Array("A1:J10", "K11:T20", "U21:AD30")
If LBound(RngArry) < UBound(RngArry) Then
For i = LBound(RngArry) To UBound(RngArry)
ActiveSheet.Range(RngArry(i)).Clear
Next
End If
End Sub

This clears the cells in the defined ranges. Replace the ranges in Array("A1:J10", "K11:T20", "U21:AD30") with your own. You can
add/delete ranges as you like.

Cheers

| I need a macro that will basically zero out all cells in three different
| ranges. i cant get it to work.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200702/1

Change the ActiveSheet.Range(RngArry(i)).Clear

to ActiveSheet.Range(RngArry(i)).ClearContents

which will not clear your formatting.

If you do want zeros...................

Sub ClearIt()
Dim i As Integer
Dim RngArry As Variant
RngArry = Array("A1:J10", "K11:T20", "U21:AD30")
If LBound(RngArry) < UBound(RngArry) Then
For i = LBound(RngArry) To UBound(RngArry)
ActiveSheet.Range(RngArry(i)).Value = 0
Next
End If
End Sub



Gord Dibben MS Excel MVP



On Mon, 19 Feb 2007 12:48:58 GMT, "brownti via OfficeKB.com" <u31540@uwe wrote:

This works for me, but it changes all of my formating, is there a way to
instead of clearing the cells, to just enter a zero instead of the number
that is in the cell?


macropod wrote:
Hi,

You could use something along the lines of:

Sub ClearIt()
Dim i As Integer
Dim RngArry As Variant
RngArry = Array("A1:J10", "K11:T20", "U21:AD30")
If LBound(RngArry) < UBound(RngArry) Then
For i = LBound(RngArry) To UBound(RngArry)
ActiveSheet.Range(RngArry(i)).Clear
Next
End If
End Sub

This clears the cells in the defined ranges. Replace the ranges in Array("A1:J10", "K11:T20", "U21:AD30") with your own. You can
add/delete ranges as you like.

Cheers

| I need a macro that will basically zero out all cells in three different
| ranges. i cant get it to work.