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
WorkRng.Select
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
ErrorHandler:
' 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!