VBA Set Column Width or Row Height
Excel Row heights and Columns widths can be changed in VBA by setting the .RowHeight and .ColumnWidth properties.
Set Column Width with VBA
Macro to set the column width of Columns A to E:
Columns("A:E").ColumnWidth = 30
Set Row Height with VBA
Macro to set the row height of Row 1:
Rows("1:1").RowHeight = 30
Autofit Column Width
Excel offers the ability to “Autofit” column widths. This feature adjusts the column width so that the column(s) is wide enough to fit all text found in that column.
To Autofit column widths in VBA:
We wrote more about this in another article on how to Autofit a Column from VBA, including how to Autofit all used columns.
Autofit Row Height
You can also autofit row heights using a similar method:
<h2>Set Cell Width</h2>
You can also adjust column widths by referencing a cell:
<pre>Range("a1").EntireColumn.ColumnWidth = 20
Set Cell Height
Or adjust row heights by referencing a cell:
Range("a1").EntireRow.RowHeight = 10
Obtain Column Width
To obtain the column width of a column:
dim iColumnWidth as long
iColumnWidth = columns("a").ColumnWidth
Note: This will return Null if all columns in the range do not have the same width.
Obtain Row Height
Similarly, you can obtain the row height:
dim iRowHeight as long
iRowHeight = rows("1").RowHeight
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Did you find this VBA tutorial useful? Then share it with your friends and colleagues: