To showcase how to use (1) Autofit (2) change column width and row height (3) merge cells (4) Wrap text
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.
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.
Sub RowAutofit() 'Autofit all the rows and columns Rows.AutoFit Columns.AutoFit End Sub
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.
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.
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
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.
Sub MergeCells() Application.DisplayAlerts = False 'Merge cells using VBA Range("A1:B1").MergeCells = True End Sub
Post you may like