Converting number stored as text

[rrocker1405]

Hi,

Im using the following code to convert the number stored as text to number. However, I get out of memory run-time error 7. Am I doing something incorrect here?

Range("A2:BZ65000").Select 'specify the range which suits your purpose
With Selection
Selection.NumberFormat = "General"
.Value = .Value
ActiveSheet.Cells.ClearFormats
Range("a2").Select
End With

debugging points to .value = .value

Thanks in advance.

Kind regards,
Anand

 

[Smallman]

Hi @rrocker1405

It is a good idea to not select the Data in VBA as your coding should run more quickly. I tested your code and it worked on my machine. What does your data look like and do you really have 65K rows of data. Maybe trapping a dynamic range in VBA.

Sub testo()
Dim rng As Range
Set rng = Set rng = Range("A2:BZ" & Cells.Cells(Cells.Rows.Count, 1).End(xlUp).Row)
 
    rng.NumberFormat = "General"
    rng.Value = rng.Value
    rng.ClearFormats
End Sub

Should make your life a bit easier. If it does not work then it is your date as it works at this end.

Take care

Smallman

 

[NARAYANK991]

Hi Anand ,

A out of memory message is a genuine error message ; it is very likely that the code segment you have posted has nothing to do with it.

If you have a Worksheet_Change event procedure also in your workbook , then using the segment :

.Value = .Value

without disabling the Worksheet_Change event like this :

Application.EnableEvents = False
.Value = .Value
Application.EnableEvents = True

will crash Excel.

Narayan