Macro using the F2 key.

[alexaronson]

I have a large mass of data.
Some cells have values like this "8492".
Others have vlues like this "00009".

I have to get the cells to drop the leading zeros.
I have tried changing the format of the cells to numbers, but the zero's wont drop.

I know if I select the cell one at a time and hit the F2 key, the zero's magically disappear.

Due to the size of the data, I can't F2 every cell.

What I am looking for is a macro, that will go through a column of data, select the data, hit the F2 key, and then go to the next cell.

I created a macro that mimincs what I do manually and this is what I get.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/17/2005 by aaronso
'

'
    ActiveCell.FormulaR1C1 = "26909"
    Range("D279").Select
    ActiveCell.FormulaR1C1 = "26910"
    Range("D280").Select
    ActiveCell.FormulaR1C1 = "27030"
    Range("D281").Select
    ActiveCell.FormulaR1C1 = "27033"
    Range("D282").Select
    ActiveCell.FormulaR1C1 = "28018"
    Range("D283").Select
End Sub

So with that thought process I tried this:

Sub CleanZeros()
Application.ScreenUpdating = False

For j = 1 To 40000
ActiveCell.FormulaR1C1 = Row()
       j = j + 1
           
Next
Application.ScreenUpdating = False
End Sub

Can anyone help?

 

[erik.van.geit]

Hi, alexaronson,
you don't necessarely need a macro

try one of these methods
method 1.
select data
menu data / text to columns / finish (whatever settings are there)
method 2.
type 1 in any cell (or type 0)
copy there cell
select data
rightclick
choose pastespecial
paste values + check multiply (or add)

kind regards,
Erik

 

[alexaronson]

Solution worked great.


Thanks Erik!!