Welcome To Macro Mondays again Excellers. I want to share another Excel Tip in my 2019 blog post series. Today let’s look at a really useful macro which will TRIM spaces from a user selected range on your Excel worksheet.
If you want to know when I have a new YouTube Video, Blog Post, Tweet, or Facebook post then check out my social media links below and hit that SUBSCRIBE button.
Preparing To Write The Macro
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice. You can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Starting The Macro
Ok on to today’s awesome Macro. This will be so useful when you import data from other sources, or sometimes when you have performed text To Columns or Find and Replace, or in fact, do any other data clean up or inherited data, the dreaded extra spaces either at the start or the end of the cell. Sometimes you don’t even notice them until you start to carry our data analysis and you realize things look a little strange. Well having this little power packed Macro can blitz those cell spaces with one click.
We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsbworkbook, then Insert Module.
Type Sub then the name of your macro. In this example, I have called the macro RemoveSpaces. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.
[stextbox id=’info’]Sub RemoveSpaces
End Sub[/stextbox]
Declaring Variables
The next step in writing this macro is to declare any variables we need. This ensures that Excel creates a memory container for these values. In this example, we have two variables to declare.
The First is MyRange to hold the entire target range we to remove paces from and MyCell to hold each cell in the range as the macro runs through each of them one by one.
[stextbox id=’info’]
Dim MyRange
Dim MyCell
[/stextbox]
Setting The Range
This part of the macro fills up or uses the variable with the target range that the user has selected.
[stextbox id=’info’] Set MyRange = Selection
[/stextbox]
Using The For Each Loop With IsEmpty
This is where Excel steps through each of the cells in the selected range and as it is activated the macro then applies the IsEmpty function to make sure that the cell is not empty, if the cell is not empty then the TRIM function is applied to the cell which removes any extra spaces at the start or the end of the cell.
[stextbox id=’info’]
For Each MyCell In MyRange
If Not IsEmpty(Mycell) Then
MyCell = Trim(MyCell)
End If
Next MyCell
[/stextbox]
Ending The Macro
The code ends once all looping of cells has been completed with the “End Sub” piece of code. This was already entered into the module for us when started the type the name of the macro.
[stextbox id=’info’]
End Sub
[/stextbox]
Have you any Excel Tips you want to share?. Leave a comment in the box below.
More #MacroMonday Blog Posts
Write A Macro To Copy Filtered Rows To A New Excel Workbook
Write An Easy Excel Macro To Change Row Height
Create Multiple Worksheets From A List Of Cell Values – Using A VBA Excel Macro
If you want more tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Macro Mondays Blog Posts.
