PDA

View Full Version : Clear constants in a selection



sujittalukde
05-22-2008, 06:42 AM
I am using the following code to clear the constants in selection.


Sub clrconstants()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula <> True Then
cell.Clear
End If
Next
End Sub


Now the selection is done manually which I want that code should select the range automatically upto the last used column and last used row.

Row 1 should be spared as it is header row.

A workbook is attached for ready reference.

L@ja
05-22-2008, 07:06 AM
Hi,
insert this code before your code:

lastcol=cells(1,1).end(xltoright).column
lastrow=cells(1,1).end(xldown).row
range(cells(2,1),cells(lastrow,lastcol)).select

Andy Pope
05-22-2008, 07:23 AM
Another version


Sub clrconstants()

With Range("A1").CurrentRegion
With .Offset(1, 1).Resize(.Rows.Count - 2, .Columns.Count - 1)
.SpecialCells(xlCellTypeConstants).Clear
.SpecialCells(xlCellTypeBlanks).Clear
End With
End With

End Sub

sujittalukde
05-23-2008, 04:17 AM
Thanks Andy, your code is working perfectly.
L@ja, your code is also doing well but clearing the data of column 1 also which I want that code should not clear.
Anyway Andy's code is allright.