Format all numbers with NumberFormat '0.0' using VBA

Dear Experts:

On the active worksheet I would like to search for ALL numeric values (even if they have been entered as text) and apply the following format: NumberFormat '0.0', ie. format all numeric values as numbers with one decimal place.  

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
Try this,

Thomas
Sub agdsagds()
Dim cl As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False


For Each cl In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    If IsNumeric(cl) Then
        cl.NumberFormat = "0.0"
        cl.Value = CDbl(cl.Value)
    End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Thomas,

great, thank you very much for your professional help. One more thing, how would this macro to be tweaked if not the used range would be used but a workbook level scope name, named 'MyRange'

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
 
nutschConnect With a Mentor Commented:
You'd replace

For Each cl In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)

by

For Each cl In activeworkbook.range("MyRange").SpecialCells(xlCellTypeConstants)

Thomas
0
 
Andreas HermleTeam leaderAuthor Commented:
Great, Thomas. I really appreciate  you swift and professional support.

Thank you very much. Regards, Andreas
0
 
nutschCommented:
Glad to assist. Thanks for the kind words.

Thomas
0
All Courses

From novice to tech pro — start learning today.