Hello, Excellers. Welcome back to another Excel blog post in my 2019 series of #macromonday. Today, I will share with you a quick bit of Excel VBA code. It changes the height of our rows or Excel cells super fast. This macro proved useful to me. Every week I receive a data sheet that I have to manipulate and alter the row height. So, after a few weeks, yes that is right I just had to write a bit of code as a solution to this. You can see a sample of some of the Excel data I have to adjust every week. The row headers always do not fit correctly (only one of the numerous issues with the Excel workbook). Each can be solved easily with some code. But we will concentrate on the row height in this example.
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
We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it RowHeight. 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 RowHeight()
End Sub
[/stextbox]
Setting The Range and Row Height
We use the range object which represents one or more cells on the worksheet. In this example, we are specifying the range A1:C1. Using the RowHeight property which sets the height for all rows in a range of cells. I have chosen to set the row height at 60.
[stextbox id=’info’]
Range (“A1:C1”).RowHeight = 60
[/stextbox]
Ending The Macro
Once the height of the cells in the range has been changed, the code finally ends 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]
More Related #MacroMonday Blog Posts
Write A Macro To Copy Filtered Rows To A New Excel Workbook
VBA Sub and Function Procedures- Explanation and Examples – Macro Monday
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