vb to clear cell contents

  • Thread starter Thread starter RichT
  • Start date Start date
R

RichT

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.
 
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
 
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
 
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.
 
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.
 
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
 
Back
Top