Excel Macro - Clear cells

Excel Macro - Clear cells

Didn't find your answer?

Is it possible to write a macro that will automate the following in a worksheet:

Firstly, I would like to paste value each cell that starts with the formula '=HPVAL'

Secondly, I would like to clear each cell that starts with the formula '=IF'

Finally, I would like to clear each cell which start with 'P' and is followed by 5 numeric numbers i.e. P60400.

Alan Ryder

Replies (3)

Please login or register to join the discussion.

avatar
By AnonymousUser
18th Jul 2006 13:34

Very minor efficiency enhancement
over Alan's suggestion might be to use

aCell.Formula = aCell.Value

instead of

aCell.Copy
aCell.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

(not tested, but feel it should run a bit faster).

Thanks (0)
avatar
By ACDWebb
17th Jul 2006 21:37

Not the most elegant but
I think this does it

'code starts
Sub DoIt()
Dim aCell
Dim aRef As Boolean
For Each aCell In ActiveSheet.UsedRange.Cells
aRef = False
If Left(aCell.Formula, 6) = "=HPVAL" Then
aCell.Copy
aCell.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
If Left(aCell.Formula, 3) = "=IF" Then aCell.ClearContents
If Len(aCell) = 6 _
And _
Left(aCell, 1) = "P" _
And _
IsNumeric(Mid(aCell, 2, 1)) _
And _
IsNumeric(Mid(aCell, 3, 1)) _
And _
IsNumeric(Mid(aCell, 4, 1)) _
And _
IsNumeric(Mid(aCell, 5, 1)) _
And _
IsNumeric(Mid(aCell, 6, 1)) Then aRef = True
If aRef Then aCell.ClearContents
Next aCell
End Sub
'code ends

Thanks (0)
avatar
By AnonymousUser
27th Nov 2006 09:57

Code used in Final macro
Many thanks for the help guys. Below is the code I used in the final macro.

Dim aCell

For Each aCell In ActiveSheet.UsedRange.Cells

If Left(aCell.Formula, 6) = "=HPVAL" Then aCell.Formula = aCell.Value

If Left(aCell.Formula, 7) = "=-HPVAL" Then aCell.Formula = aCell.Value

If Left(aCell.Formula, 3) = "=IF" Then aCell.Formula = ""

If Left(aCell.Text, 1) = "P" And IsNumeric(Mid(aCell.Text, 2, 5)) Then _
aCell.Value = ""

Next aCell

Thanks (0)