Macro 54: Create a Super Data Cleanup Macro
As we mentioned at the beginning of this Part, you can combine the macros we have covered thus
far into a super data cleanup macro. This allows you to clean and standardize your data in one fell
swoop, saving loads of time and headaches.
How it works
In this macro, we combine several of the data transformation macros we have covered into one. Note
that we only need to declare the two Range object variables one time. With each action, we point
these variables to different ranges. Although you have to alter the ranges and the tasks in this macro,
it gives you a good idea of how to start an all-purpose data cleanup procedure that suits your needs.
Sub Macro54()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells?
Select Case MsgBox(“Can’t Undo this action. ” & _
“Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
‘Step 3: Perform Text to Columns
Set MyRange = Range(“F6:I17”)
For Each MyCell In MyRange
If Not IsEmpty(MyCell) Then
MyCell.Value = MyCell.Value
End If
Next MyCell
‘Step 4: Pad Customer Numbers with zeros
Set MyRange = Range(“B6:B17”)
For Each MyCell In MyRange
End If
Next MyCell
‘Step 5: Truncate Postal Codes to 5 digits
Set MyRange = Range(“C6:C17”)
For Each MyCell In MyRange
If Not IsEmpty(MyCell) Then
MyCell = Left(MyCell, 5)
End If
Next MyCell
‘Step 6: Add Area code to Phone Numbers
Set MyRange = Range(“D6:D17”)
For Each MyCell In MyRange
If Not IsEmpty(MyCell) Then
MyCell = “(972) ” & MyCell
End If
Next MyCell
‘Step 7: Trim Spaces from Product Numbers
Set MyRange = Range(“E6:E17”)
For Each MyCell In MyRange
If Not IsEmpty(MyCell) Then
MyCell = Trim(MyCell)
End If
Next MyCell
‘Step 8: Replace Blanks with zeros
Set MyRange = Range(“F6:I17”)
For Each MyCell In MyRange
If Len(MyCell.Value) = 0 Then
MyCell = 0
End If
Next MyCell
End Sub
How to use it
To implement this kind of a macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
