ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Zero out (https://www.excelbanter.com/excel-discussion-misc-queries/131100-zero-out.html)

brownti via OfficeKB.com

Zero out
 
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


macropod

Zero out
 
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
|



brownti via OfficeKB.com

Zero out
 
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


Gord Dibben

Zero out
 
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.




All times are GMT +1. The time now is 12:00 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com