Clean Data With Vba

What this code allows you to do is circumvent testing (ie looping) each individual cell and handling trimming (removing leading and ending spaces) and cleaning (removing unprintable characters) process for your Excel data. It’s a great way to clean up your data getting exported from an outside database.

'==================================================================================================
' ## Clean up your data with TRIM, CLEAN and SUBSTITUTE using VBA
'==================================================================================================
Sub TrimCells()
    '// Vars
    Dim rngArea             As Range
    Dim rngSelection        As Range
    Dim rngInitialSelect    As Range
    Dim strPrompt           As String

    '// Test a range selected
    If TypeName(Selection) <> "Range" Then Exit Sub

    '// Prompt the user to select a range
    strPrompt = "Select the range to trim cells" & vbNewLine & _
                "Click cancel to quit this task"

    On Error Resume Next
        Set rngInitialSelect = Application.Selection
        Set rngSelection = Application.InputBox(strPrompt, "Trim Cells", rngInitialSelect.Address, Type:=8)
        Set rngSelection = Intersect(rngSelection, _
                        rngSelection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
    On Error GoTo 0

    '// Check if user clicked cancel
    If rngSelection Is Nothing Then Exit Sub

    '// Optimise
    Application.ScreenUpdating = False

    '// Trim Clean and use Substiture on cell values
    For Each rngArea In rngSelection.Areas
        rngArea.Value = Evaluate("IF(ROW(" & rngArea.Address & "),CLEAN(TRIM(SUBSTITUTE(" & rngArea.Address & ",CHAR(160)," & " " & "))))")
    Next rngArea

    rngSelection.Select
    '// Optimise
    Application.ScreenUpdating = True
End Sub