CODEWITHSUNDEEP
vbafor-loop
Yasmine Nouri
Yasmine Nouri

Reputation: 103

How to improve performance of vba code that converts textnumber to number

How could we improve performance (runtime-efficiency) of following code that converts textnumber to number of data situated in the range(X76:BE100000)?

Sub ConvertTextNumberToNumber()
   For Each r In     
      Worksheets("Sheet1").Range("X76:BE100000").SpecialCells(xlCellTypeConstants)
      If IsNumeric(r) Then r.Value = Val(r.Value)
   Next

End Sub

Upvotes: 0

Views: 38

Answers (2)

user3598756
user3598756

Reputation: 29421

use arrays

Option Explicit

Sub ConvertTextNumberToNumber()
    Dim dataArr As Variant
    Dim myRng As Range
    Dim i As Long, j As Long

    Set myRng = Worksheets("Sheet1").Range("X76:BE10000")

    dataArr = myRng.Value2

    For i = 1 To UBound(dataArr, 1)
        For j = 1 To UBound(dataArr, 2)
            If IsNumeric(dataArr(i, j)) And dataArr(i, j) <> "" Then dataArr(i, j) = CDbl(dataArr(i, j))
        Next j
    Next i

    myRng.Value2 = dataArr
End Sub

Upvotes: 2

Nathan_Sav
Nathan_Sav

Reputation: 8531

Range("a1:zz100000").SpecialCells(xlCellTypeConstants, xlNumbers)

Upvotes: 2

Related Questions