Hello Excellers, welcome to another #MacroMonday #Exceltip in my 2021 Excel series to use . Today’s Excel Macro tip is a really simple but very effective time saver to trim extra spaces in Excel. I used this quite recently and thought it would be a great one to share.
My sample workbook below has over 50 worksheets. Unfortunately, like many inherited Excel workbooks, it appears that the worksheet names all have extra spaces in their names. These additional spaces can affect any formulas that may contain the worksheet names. (This is how I discovered there was an issue with extra spaces!). So, this Excel macro will trim the worksheet names.
The VBA version of this function is the simliar to the TRIM Function in Excel in that it removes leading and trailing spaces, but not extra spaces space in between words. Only the Excel TRIM function removes all three types of extra spaces.
So, let’s get going and write the Excel Macro. Here is the code.
Sub TrimWorksheetNames()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Name = Trim(WS.Name)
Next
End Sub
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 choose to either create a module to store your code either in your Personal Macro Workbook or in your current workbook. If you want to reuse your macro in mutiple workbooks at any time, then save it in your Personal macro workbook. Choose to insert and save the macro in the current workbook, yes that’s right it will only be available to use in that particular workbook. So, in this TRIM Excel macro, we may use it over and over again. I will therefore save in my Personal Maro Workbook.
If you have not already done so insert a new module.
- Developer Tab | Code Group | Visual Basic
- Personal Macro Workbook | Insert Module
Step 1. Name The Macro. Trim Excel Worksheet Names.
The first stage is to name your procedure. In this exmaple I have called this macro TrimWorksheetNames. Nice and simple and easy to reuse when I need to.
Sub TrimWorksheetNames
End Sub
Notice that Excel will automatically insert the End Sub line of code once the macro has been named. All other lines of code go inbetween these two lines.
Step 2. Declare Variables.
The next stage is to declare any variables that we are going to use in the macro. This simply creates a type of memory contaoner in Excel to store these values. In this procedure I am going to declare one variable and that is the worksheet.
Dim WS As Worksheet
Step 3. Loop Through Worksheets And Trim.
Next, the For Each Next looping method used. This loops though all objects in a collection. In this case it is all of the worksheets in the Excel workbook. As each workbook is looped the TRIM function removes extra leading and trailing spaces from the worksheet name.
For Each WS In Worksheets
WS.Name = Trim(WS.Name)
Next
This continues until all worksheet names have been trimmed.
Step 4. Ending The Macro.
Finally the code ends when all worksheet names have been trimmed of extra leading and trailing spaces. Excel already inserted this line of code on the first step of the macro.
End Sub
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money-back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.
Read All Of The Blog Posts In My Formula Friday Series And My Macro Monday Series.