Change column format to numbers (ignore blanks and text)

[LeanneBG]

Hi Experts - I looked around and was able to build below code.. My goal is to convert the format of the numbers found in column R (that are in general/text format) into number. The line item is changing so i don't have the "fixed" number of the last cell with data.

I also need the code to ignore blanks and texts/strings and to only convert the numbers. I was able to successfully do it using below code, however, it takes a long time (sometimes excel is already crashing) to do it because i think it is trying to look until the very last row of the excel (even those that has no data anymore). Can you please help check where i went wrong? Appreciate the help. Thank you in advance

Sub FormatCells()
Application.ScreenUpdating = False
Dim rng As Range, cell As Range, LastRowInR As Long






Set rng = ThisWorkbook.Sheets("SRPT").Range("R:R")


For Each cell In rng
    If Not IsError(cell.Value) Then _
        If Len(cell.Value) <> 0 And cell.HasFormula = False And _
            IsNumeric(cell.Value) Then cell.Value = Val(cell.Value)
Next cell


Application.ScreenUpdating = True
End Sub

 

[James006]

Hello,

To get the last you should following

[COLOR=#333333]LastRowInR = [/COLOR]ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row

provided Column A has the correct number of Rows

then

[COLOR=#333333]Set rng = ThisWorkbook.Sheets("SRPT").Range("R2:R"& LastRowinR)[/COLOR]

Hope this will help

 

[LeanneBG]

Hello,

To get the last you should following

[COLOR=#333333]LastRowInR = [/COLOR]ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row

provided Column A has the correct number of Rows

then

[COLOR=#333333]Set rng = ThisWorkbook.Sheets("SRPT").Range("R2:R"& LastRowinR)[/COLOR]

Hope this will help

it works! although still a little bit slow but much better compared to the first code. Thanks a lot James006!!

 

[James006]

Great ...

In order to speed up your macro :

Just insert as your first instruction :

Application.Calculation = xlCalculationManual

and as your last instruction :

Application.Calculation = xlCalculationAutomatic

Hope this will help