Losing Formatting after Maco Runs

[jeran042]

I have a simple macro that trims and changes the case of a cell from lower to UPPER, however when I run this macro and the cell is inside of a table, it seems to remove the table. The values remain, but it is converted back to just a range. Here is what my code looks like. Is there any way to preserve the condition and just change the Case?

Sub Trim_And_UPPER()  
ActiveSheet.UsedRange = Evaluate(Replace("IF(@="""","""",UPPER(TRIM(@)))", "@", ActiveSheet.UsedRange.Address))
End Sub

Much appreciated!

 

[pgc01]

Hi

I believe that you have to loop through the cells and change the values.

 

[Llupo01]

Hello,

try this

Sub Capital_letters()
    
    Application.ScreenUpdating = False

    Dim cell As Range
    ' here set your range
    For Each cell In Range("C2:D2")
        If Len(cell) > 0 Then cell = UCase(cell)
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[jeran042]

That is where I was headed, I came up with:

Sub MyUpperCase()    
    Application.ScreenUpdating = False


    Dim cell As Range
    For Each cell In Range("$A$1:" & Range("$A$1").SpecialCells(xlLastCell).Address)
        If Len(cell) > 0 Then cell = Trim(UCase(cell))
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

Thank you all for your help!