Zero out

  • Thread starter brownti via OfficeKB.com
  • Start date
B

brownti via OfficeKB.com

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

macropod

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]


| 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.aspx/ms-excel/200702/1
|
 
B

brownti via OfficeKB.com

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?
 
G

Gord Dibben

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
 
Top