Highlight Cells with a Single Space Using Macros In Excel

If you had a spreadsheet with a lot of single spaces, it is very annoying because it is hard to see. You can use Excel Macros to highlight cells with a single space easily!

Make sure your Excel has the Developer Tab enabled following this tutorial.

I explain how you can do this below step by step!

What does it do?

Highlights all cells with a single space

Copy Source Code:

Sub HighlightTheSingleSpaces()

Dim range As Range

For Each range In ActiveSheet.UsedRange
'Check if it's a single space, then change the style to be Note
If range.Value = " " Then
range.Style = "Note"
End If
Next range

End Sub

Final Result: 

Exercise Workbook:

DOWNLOAD EXCEL WORKBOOK

This is our initial set of data, the ones enclosed in red are the ones with a single space:

STEP 1: Go to Developer > Code > Visual Basic

STEP 2: Paste in your code and Select Save. Close the window afterwards.

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

Make sure your macro is selected. Click Run.

With just one click, all of the cells with a single space are now highlighted!

How to Highlight Cells with a Single Space Using Macros In Excel

If you like this Excel tip, please share it

email

Pinterest

Facebook

Twitter

Linkedin