Sub Clear()
Dim r as long, nr as long
Dim j as integer, ncol as integer.
nr = Range("B2").currentregion.rows.count
ncol = Range("B2").currentregion.rows.count
Range(cells(2,2),cells(nr,ncol).clearcontents
End sub

Peter Atherton

"RichT" wrote:


Hi,
Looking for some very basic VB assistance.

I use a range of cells to hold some temporary data, after which I clear
theas range with a macro.
The range always begins at "B4"and has a fixed number of columns, but
the number of rows could be between 1 and 15.
I currently do this (messily) as follows:

Sub ClearCells1()
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("B4").Select
End Sub

which I have now discovered doesn't work if there's only 1 row of
data,
and is lethal if there's no data in the range.
Any assistance would be greatly appreciated.


--
RichT
------------------------------------------------------------------------
RichT's Profile: http://www.excelforum.com/member.php...o&userid=23678
View this thread: http://www.excelforum.com/showthread...hreadid=380548