How do I make the space before the text disappear without making the space between the texts disappe-CodePudding

How do I make the space before the text disappear without making the space between the texts disappear on every sheet ? I've tried to come up with the following code.

    Public Sub Test()
    Dim rng As Excel.Range
    For Each rng In ActiveSheet.UsedRange 'or change to something like ActiveSheet.Range("A1:A100") for a specific range
        rng.Value2 = Trim(rng.Value2)
    Next
End Sub

But it's really slow and will only apply to the first sheet out of my 3 sheets. Basically I want to change a cell like " Total Revenue" into "Total Revenue" and would like to apply my code on all 3 sheets I, B and C. Thank you guys in advance !

CodePudding user response：

Trim Ranges

Basic

Note that this will convert any formulas to values.

Sub TrimAllWorksheetsBasic()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' or:
    'Set wb = ActiveSheet.Parent ' workbook of the active sheet
    
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        ws.UsedRange.Value = Application.Trim(ws.UsedRange.Value)
    Next ws

End Sub

Only Cells With Values

Sub TrimAllWorksheets()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' or:
    'Set wb = ActiveSheet.Parent ' workbook of the active sheet
    
    Dim ws As Worksheet, rg As Range, arg As Range
    
    For Each ws In wb.Worksheets
        On Error Resume Next
            Set rg = ws.UsedRange.SpecialCells(xlCellTypeConstants)
        On Error Goto 0
        If Not rg Is Nothing Then
            For Each arg In rg.Areas
                arg.Value = Application.Trim(arg.Value)
            Next arg
            Set rg = Nothing ' reset for the next iteration
        End If
    Next ws

End Sub

Specific Worksheets

Sub TrimSpecificWorksheets()
    
    Dim TrimSheets(): TrimSheets = Array("I", "B", "C")
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' or:
    'Set wb = ActiveSheet.Parent ' workbook of the active sheet
    
    Dim ws As Worksheet, rg As Range, arg As Range
    
    For Each ws In wb.Worksheets(TrimSheets)
        On Error Resume Next
            Set rg = ws.UsedRange.SpecialCells(xlCellTypeConstants)
        On Error Goto 0
        If Not rg Is Nothing Then
            For Each arg In rg.Areas
                arg.Value = Application.Trim(arg.Value)
            Next arg
            Set rg = Nothing ' reset for the next iteration
        End If
    Next ws

End Sub