Welcome To Macro Mondays again. If you want to go learn more about Excel Macro then check out the awesome course by Allen Wyatt – I don’t receommend many courses, but this is one of my few exceptions. I loved it. Click the picture below for more information,
If you want to download the corresponding Excel example file for this blog post you can do so here.
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.
Ok on to todays awesome Macro, which will be so useful when you import data from other sources, or sometime when you have performed text To Columns or Find and Replace, or in fact done 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 realise things look a little strange. Well having this little power packed Macro can blitz those cell spaces with one click.
What Does The Macro Do?
This macro will search out and remove extra spaces at the beginning and the end of the cell, or range of cells you select in your Excel worksheet.
How Does It Work?
The macro will run though each of the cell or cells you have chosen in your range and apply the TRIM function to it. Hence removing those extra spaces. Awesome. Let’s get writing.
FIRST Create A Module. Open Visual Basic – by hitting F11 or Developer Tab – Visual Basic
Step 1. Declare Our Variables. We have two Range Object Variables in this Macro. 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.
Step 2. This part of the macro fills up the MyRange variable with the target range that the user has selected.
Step 3. This is where Excel steps through each of the cells in the selected range and as it is activiated 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.
Step 4. This last part of the macro loops through to the next cell, and once all of the cells in the target range have been activiated then the macro ends.
Here is the code for the Macro.
Other Blog Posts You Might Like
Macro Mondays – What are the Three Windows In The Visual Basic Editor For?
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.