Converting MANY columns and rows from Text to Number

[some_evil]

Hi Guys,

I am getting data supplied from my database as .xlsx and when it is opened most of the cells are text, so they all need to be converted to number to be able to used in many of my formulas/vlookups etc.



What I am finding is that if I highlight too many cells the little button does not appear. In the below screen grab it IS appearing, but I find if I select more than say 2000 cells the option doesn't appear.



What I have found is a great bit of code which works well, but it is VERY slow.. it basically goes cell by cell converting one by one. Probably about 3 cells per second, and some workbooks have about 50,000 cells to be converted, so I am looking for a better piece of code to do this. The code I am using at the moment is pasted below.

Sub Convert2Number()


For Each xCell In Selection


Selection.NumberFormat = "0" 'Note: The "0.000" determines the number of decimal places.


xCell.Value = xCell.Value


Next xCell


End Sub



One thing to note is this PC is less than 12 months old and is very powerful as it runs our GIS/Mapping/Cad software with ease.

Can anyone help me speed my code up?

Thanks very much.
-Cheers

[some_evil]

Ok to answer my own question, I think I have found a much quicker (and I literally mean it takes < 1 second to complete) solution...

It seems to work a treat!

Sub ConvertSelectedText2Numbers()
 'Convert all cells with numbers from text to numbers
    Dim rUsedRange As Range
    For Each rUsedRange In Intersect(ActiveSheet.UsedRange, Selection).Areas
    rUsedRange.Value = rUsedRange.Value
    Next rUsedRange
End Sub

Thanks anyway, I hope this thread can help someone in the future! Goodluck future person!

[Ken Puls]

Thanks for posting back!

[snb]

Did you try ?

Sub M_snb()
  usedrange.value=usedrange.value 
End Sub

[some_evil]

Did you try ?

Sub M_snb()
  usedrange.value=usedrange.value 
End Sub

Are you able to elaborate on how this one works? Do I simply run this small macro and it searches the entire document for fields that can be converted from text to number? Do I have to have them selected?? Etc

The reason I ask is that the current code needs to have a selection before running I believe. Is the code you provided a step up from this?

Sub ConvertSelectedText2Numbers() 'Convert all cells with numbers from text to numbers
    Dim rUsedRange As Range
    For Each rUsedRange In Intersect(ActiveSheet.UsedRange, Selection).Areas
    rUsedRange.Value = rUsedRange.Value
    Next rUsedRange
End Sub

Thanks, as you can probably tell I am a macro n00b at this stage, trying to find my way!

Cheers