some_evil
New member
- Joined
- Feb 10, 2014
- Messages
- 10
- Reaction score
- 0
- Points
- 1
- Excel Version(s)
- 2019
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
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