Cleaning Up Excel Columns With VBA

Never deal with crazy delimeter problems again!

Posted by JDFtheTech on February 28, 2018

When uploading data and working with Excel, CSV and tab delimeted files, you can sometimes run into strange formatting issues. This is especially true when trying to use things like Index Matching, Vlookups or Hlookups. If a format inconsistency arises, #N/A errors will abound and it can be quite a pain if a simple click of the format button doesn’t take care of the issue.

This has been a common problem of mine for years, however I wrote a solution long ago. Today I share with you what I call the ‘Clean Blanks Select’ script. I have used it for years to quickly fix columns where perhaps one file type decided to put a hidden space or an apostrophe in a long list of data.

To use this, you will need to have your Excel developer options enabled. You will also need to have an understanding of how to import macros and save them. I set this up as a button in my ribbon for ease of access, and I recommend that as the default process.

To keep things neat for today, I’ll just post the code and not go into how to set up ribbon buttons or create macros. All code examples I post will always be stored on my Github account at the bottom of the page, so feel free to hop in there. There are lots of other code examples I will write about in the future, so dig around!

On to the code:

Sub cleanBlanksSelection()

' set a variable for range selection
Dim WorkRng As Range

On Error GoTo ErrorHandler
' select range, if nothing is selected, then we go to ErrorHandler
' create input box
xTitleId = "Select Cells To Clean"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

' final range select statement

With Selection

' copy cells to themselves using the NumberFormat method

Selection.NumberFormat = "General"
.Value = .Value

End With

' turns on screen updating - turn off if the system is slow or crashing

Application.ScreenUpdating = True

Exit Sub


' Tell the user there is an error with the range
MsgBox "Either you didn't select some cells, or the sheet may have another error."

Resume Next

End Sub

Feel free to make adjustments to the code, or use it as is. As always, never hesitate to reach out to me with any questions!