Speed it up if you can

[Lukums]

Morning guys -

Obviously this is looking at the ENTIRE column range it takes a good 3 minutes to run (eventually works as intended) anything I can do about identifying the LASTROW and selecting that with implying the UCASE to the selected range found?

Sub addresscaptial()

Dim Cell As Range
Columns("D:F").Select
For Each Cell In Selection.Cells
If Not Cell.HasFormula Then
Cell = UCase(Cell)
End If
Next


Call addresscaptial2


End Sub


Sub addresscaptial2()


Dim Cell As Range
Columns("Q:S").Select
For Each Cell In Selection.Cells
If Not Cell.HasFormula Then
Cell = UCase(Cell)
End If
Next


End Sub

Cheers guys,

 

[AlphaFrog]

[color=darkblue]Sub[/color] addresscaptial()
    
    [color=darkblue]Dim[/color] Cell [color=darkblue]As[/color] Range
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    Application.Calculation = xlCalculationManual
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] Cell [color=darkblue]In[/color] Range("D:F,Q:S").SpecialCells(xlCellTypeConstants)
        Cell = UCase(Cell)
    [color=darkblue]Next[/color]
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Lukums]

Legend. Works beautifully

 

[Fazza]

tweaking that a bit, to remove looping cell-by-cell

Sub addresscapital()
    
    Dim rngCells As Excel.Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    On Error Resume Next 'to handle no special cells constants found
    For Each rngCells In Range("D:F,Q:S").SpecialCells(xlCellTypeConstants).Areas
        rngCells.Value2 = Application.Evaluate("Upper(" & rngCells.Address & ")")
    Next rngCells
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub

 

[footoo]

Alternatively, format the columns with a capitals font (e.g. Arial Caps), then no VBA required.
Note: this will capitalise all text including the results of formulas.