Hello Excellers, welcome back to another #MacroMonday #Exceltips blog post in my Excel series. Today I will demonstrate how to write a very small Excel VBA macro that will quickly remove any text wrapping. The macro will allow the user to select a range of cells and then remove any text wrapping.
If there is a lot of text on an Excel spreadsheet then more than likely there will be some text wrapping. Generally an Excel cell can holds around 9 characters before you hit the border of the cell. After this the text appears truncated. To fix this, you can use the Wrap Text commands found on the Home tab of the Ribbon. Wrap Text makes text appear on multiple lines within the current column width.
Let’s write a simple Macro to remove this wrapping.
Prepare To Write The Macro
First, open the Visual Basic Editor. There are two ways to do this. Hit ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
If you cannot see the Developer Tab you may need to enable it. Check out my very short YouTube video for instructions. I will show you how to enable the developer tab in just a few minutes.
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)
Therefore, if you want to read more about your Excel PMW then check out my blog posts below.
Creating and Updating Your Personal Macro Workbook
Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Starting The Macro.
After the new module is inserted, type Sub then the name of the Excel macro. In this example, I have named my Macro RemoveTextWrapping.
[stextbox id=’info’]
Sub RemoveTextWrapping()
End Sub
[/stextbox]
Declare Variables.
Next, any variables need to be declared. This simply means that a memory container is created in Excel to store these values. In this macro, there is only one variable. We are declaring the variable rng as the range of cells to remove the text wrapping from.
[stextbox id=’info’]
Dim rng as Range
[/stextbox]
Setting The Range Of Cells.
Onto the next line of code. This where the user selected cells are defined as
[stextbox id=’info’]
Set rng = Selection
[/stextbox]
Removing Text Wrapping.
This line of code switches the property of any cells with text wrapping to False. Cells with text wrapping will have a True status. A nice simple bit of code.
[stextbox id=’info’]
rng.WrapText = False
[/stextbox]
Ending The Code.
Finally once all of the cells status are changed the macro ends. This bit of code was automatically generated as the macro was named in the first stage of writing the code. Nice.
[stextbox id=’info’]
End Sub
[/stextbox]
All you need to do not is test your macro!.
So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook, 30 Excel Tips, and check out all of my posts below.