VBA Excel | Autofit , Merge ,Row Height , Column Width ,Wrap text properties

wrap text autofit row height column width

Objective

To showcase how to use (1) Autofit  (2) change column width and row height (3) merge cells (4) Wrap text

Approach

In this VBA Excel automation, we shall showcase row and column autofit, wrap text, and merge cells on the sample cells shown below.We have executed all these codes on sheet1 of macro file.

Autofit

If we find the width of a row or column to be disproportionate, then we can change it by using autofit property. As we can see in the image on left below that 2nd row had disproportionate height as compared to data, while column B and C had width which was more than the length of the text contained in these columns. Now once we have executed the VBA code, we can see that height of row 2 has been changed, while width of column B and C has also been changed.

Row column autofit vba excel

Code [Autofit]

Sub RowAutofit()

'Autofit all the rows and columns

Rows.AutoFit
Columns.AutoFit

End Sub


Wrap Text

If the text which we have entered in the cell is either hidden or coming out of cell as shown in image below on left, then we can wrap that text in those cells. If we look at the image below, we can see that data in cell B2 and C2 is not being shown properly, so we shall wrap the text inside these cells. In the image on right, we can see that text in cell B2 and C2 is visible now. Left image is before execution and right-side image reflects state after executing code.

wrap text vba excel

Code [Wrap Text]

Sub WrapText()

'Wrap Text the data in a column or in a row
Columns.WrapText = True
Rows.WrapText = True

End Sub

 

Row height and Column Width

As we can see in the left image below, that column B and C had extra width and 2nd row had surplus height. Once we execute the VBA code, we can see that column width and row height has been changed, we can see results in the image on the right as shown below. We can change the row height and column width as per our requirement, using the code mentioned below.

Row column height vba excel

Code [ Row height and column width]

Sub ColumnRowFormat()

'Wrap Text the data in a column or in a row
Columns("B:C").ColumnWidth = 20
Rows("1:3").RowHeight = 15

End Sub

 

Merge Cells

As we can see in the image below on the left side that Cell A1 and B1 were separate before executing the code, once the code was executed both the cells are merged, we can see the result in the image on right. We can merge the required cells using below shown VBA code.

merge cells vba excel

Code [Merge] 

Sub MergeCells()
Application.DisplayAlerts = False

'Merge cells using VBA
Range("A1:B1").MergeCells = True

End Sub

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofit
https://docs.microsoft.com/en-us/office/vba/api/excel.range.merge
https://docs.microsoft.com/en-us/office/vba/api/excel.range.wraptext
https://docs.microsoft.com/en-us/office/vba/api/excel.range.rowheight

Post you may like

Current Directory (CurDir) and Change Directory (Chdir) in VBA Excel