Delete cell contents if the cell contains numerical value

[wurble86]

Hi there

I am a VBA newbie. Recently I have been practising writing my own macros in VBA. I am using Excel 2010.

I have been trying to write a macro to look at two columns in a worksheet (say, columns D and E) and to delete the contents (not the formatting, and I don't want to delete the cell itself) of that cell only if it contains a numerical value (e.g. 1, 2, 256 or any other number). If it finds a cell with letters, then it wouldn't delete the contents of the cell. The cells are currently formatted as "currency" cells.

Can anyone please help me with the code I'd need? I have been trying all sorts of things and I just can't get it to work. I can write a macro to clear a range easy enough, but I am stuck on getting it to delete the right stuff. I won't embarrass myself by uploading the code that I have been using...

Thanks in advance, Adam

 

[pgc01]

Hi Adam
Welcome to the board

Are those number values the result of a formula or are they constants (or both)?

 

[jscranton]

VBA has an IsNumeric test that you can use but see this thread about its limitations:

http://www.mrexcel.com/forum/excel-questions/17013-isstring-isnumber-visual-basic-applications.html

 

[wurble86]

Hi pgc01!

Thanks for the welcome.

These numbers have been manually input by a user - e.g. someone has typed them into the cells . They are not the results of formulas.

Hope that clears things up!

 

[wurble86]

Thanks jscranton, I'll check that out!

 

[pgc01]

Hi pgc01!

Thanks for the welcome.

These numbers have been manually input by a user - e.g. someone has typed them into the cells . They are not the results of formulas.

Hope that clears things up!

OK.

Try:

Sub Test()

On Error Resume Next
Columns("D:E").SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
On Error GoTo 0

End Sub

 

[wurble86]

OK.

Try:

Sub Test()

On Error Resume Next
Columns("D:E").SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
On Error GoTo 0

End Sub

pgc01,

Thank you SOO much from saving me from my pit of failure. It worked perfectly!

I can't believe that I spent so long trying to figure that out, and in the end the code looks so simple.

Thanks again!

 

[pgc01]

YOu're welcome. Thanks for the feedback.

 

[wigarth]

OK.

Try:

Sub Test()

On Error Resume Next
Columns("D:E").SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
On Error GoTo 0

End Sub

You HAve No IDEA how much work you saved me..... Thanks LOADS!!!