Trim all cells within a workbook(VBA)

excel, excel-addins, vba

Solution

Try this

Sub DoTrim(Wb As Workbook)
    Dim aCell As Range
    Dim wsh As Worksheet
    
    '~~> If you are using it in an Add-In, it is advisable 
    '~~> to keep the user posted :)
    Application.StatusBar = "Processing Worksheets... Please do not disturb..."
    DoEvents
    
    Application.ScreenUpdating = False
    
    For Each wsh In Wb.Worksheets
        With wsh
            Application.StatusBar = "Processing Worksheet " & _
                                    .Name & ". Please do not disturb..."
            DoEvents
            
            For Each aCell In .UsedRange
                If Not aCell.Value = "" And aCell.HasFormula = False Then
                    With aCell
                        .Value = Replace(.Value, Chr(160), "")
                        .Value = Application.WorksheetFunction.Clean(.Value)
                        .Value = Trim(.Value)
                    End With
                End If
            Next aCell
        End With
    Next wsh
    
    Application.ScreenUpdating = True
    Application.StatusBar = "Done"
End Sub

Problem

I have attempted to add functionality to an excel add-in ave been developing which trims the leading spaces at the end of used cells, and maybe even parse the text, The reason I need to do this is simply to have it turn into a hyperlink which I have already working but that parts fine. This is what I have attempted so far, I have it trimming the `active.worksheet` am on which is fine but I can't figure out how to: - Trim Every cell being used across the whole workbook. - And also parse the text if possible This is my attempt at Trimming the entire workbook, Its something simple I just know it, I just cant figure it out: ``` Sub DoTrim(Wb As Workbook) Dim cell As Range Dim str As String Dim nAscii As Integer Dim wsh As Worksheet For Each wsh In Worksheets With wsh.UsedRange For Each cell In ActiveSheet.UsedRange str = Trim(cell) If Len(str) > 0 Then nAscii = Asc(Left(str, 1)) If nAscii < 33 Or nAscii = 160 Then If Len(str) > 1 Then str = Right(str, Len(str) - 1) Else str = "" End If End If End If cell = str Next cell End With Next wsh End Sub ``` Any advice would be welcome am fairly new to this Language so sorry if I sound like a complete Newb! TL;DR Trims cells only worksheet am on, needs to run across whole workbook I cant figure out how to iterate it across the whole thing. EDIT: Is that also a quicker way of trimming these cells, the spreadsheets that are created for whom am designing this are massive and takes a while to trim the cells at times

Original source