Clean Data With Vba

1 minute read

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

Tags: practical, text-strings

Categories: vba

Updated: September 25, 2018

Twitter Facebook LinkedIn

Leave a Comment