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!
to the code:
' 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
' copy cells to themselves using the NumberFormat method
Selection.NumberFormat = "General"
.Value = .Value
' turns on screen updating - turn off if the system is slow or crashing
Application.ScreenUpdating = True
' 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."
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!