vb to clear cell contents
 

Hi,
Looking for some very basic VB assistance.

I use a range of cells to hold some temporary data, after which I clear
the 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

One way:

Public Sub ClearCells1()
Dim nRows As Long
Dim nCols As Long
With Range("B4")
If IsEmpty(.Value) Then Exit Sub
If IsEmpty(.Offset(1, 0).Value) Then
nRows = 1
Else
nRows = Application.Min(18, .End(xlDown).Row) - 3
End If
nCols = .End(xlToRight).Column - 1
.Resize(nRows, nCols).ClearContents
End With
End Sub



In article ,
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
the 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.

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

Many thanks for this.
It works well.
I don't really get what the following line does:-

nRows = Application.Min(18, .End(xlDown).Row) - 3

but no worries, I'm just happy it works.
Thanks again,
Richard.

JE McGimpsey Wrote:
One way:

Public Sub ClearCells1()
Dim nRows As Long
Dim nCols As Long
With Range("B4")
If IsEmpty(.Value) Then Exit Sub
If IsEmpty(.Offset(1, 0).Value) Then
nRows = 1
Else
nRows = Application.Min(18, .End(xlDown).Row) - 3
End If
nCols = .End(xlToRight).Column - 1
.Resize(nRows, nCols).ClearContents
End With
End Sub



In article ,
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
the 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

Actually, I've just discovered a problem with the solution provided. due
to my not supplying sufficient info.
My data to be deleted resides in say B4:X10, (always these columns,
but last row varies between 6 and 16).
I also have some formulae in Z4:Z10 that I don't want deleted.
The solution from JE McGimpsey unfortunately deletes these formulae.


RichT Wrote:
Many thanks for this.
It works well.
I don't really get what the following line does:-

nRows = Application.Min(18, .End(xlDown).Row) - 3

but no worries, I'm just happy it works.
Thanks again,
Richard.


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

One way:

Change

.Resize(nRows, nCols).ClearContents

to

.Resize(nRows, 23).ClearContents

and delete these lines:

Dim nCols As Long

nCols = .End(xlToRight).Column - 1


In article ,
RichT wrote:

Actually, I've just discovered a problem with the solution provided. due
to my not supplying sufficient info.
My data to be deleted resides in say B4:X10, (always these columns,
but last row varies between 6 and 16).
I also have some formulae in Z4:Z10 that I don't want deleted.
The solution from JE McGimpsey unfortunately deletes these formulae.


RichT Wrote:
Many thanks for this.
It works well.
I don't really get what the following line does:-

nRows = Application.Min(18, .End(xlDown).Row) - 3

but no worries, I'm just happy it works.
Thanks again,
Richard.

Many thanks for your help once more, JE.
It's much appreciated.


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