Excel rows and columns sometimes needs to be expanded to fit their content. How to AutoFit in Excel? Fortunately Excel allows you to AutoFit Excel rows or Excel columns to its contents readjusting the height or width. In this post I will start with showing how to resize Excel rows and columns. Then I will demonstrate the AutoFit feature. Lastly I will show how using VBA we can AutoFit multiple rows of a table.

Resizing Excel Rows & Columns

Resizing Excel rows or columns is pretty straight forward. Simply grab the border of the row or column header and resize:

Rarely would we want our columns to change their width automatically, however, Microsoft as built in a feature to make it easier for rows to re-adjust their size automatically based on the contents of cells. All we need to do is set the Wrap Text property:

AutoFit Excel Rows & Columns

To AutoFit Excel row or column, instead of grabing the border of the a header row or column Double Left Click on the border. Excel will automatically adjust the row or column size.

AutoFit Excel Table with VBA

The above explains the basics of how to resize Excel rows or columns. However, data is dynamic – it is easier to review an entire table when rows are minimized, on the other hand to read entire contents of rows of data in Excel the rows need to be AutoFitted. Switching between both these states can be a drag hence I created a simple VBA Macro that either minimizes the size of my Excel tables or AutoFits them.

Let us assume we have an Excel Table with at least 1 column which contents usually exceed the height of a single Excel row. This will look like in the image below.

What I started doing above was converting the Excel range (with my data table) to something called an Excel Table which will make it look like this:

Now that we have our table let us add 2 VBA Macros to our VBA Project:

'Reduces the row height to 15 of all rows in Excel Table
Sub Minimize()
   Dim ws As Worksheet, lr As ListRow: Set ws = ActiveSheet
   For Each lr In ws.ListObjects(1).ListRows
    If Not (lr.Range.Cells.EntireRow.Hidden) Then lr.Range.RowHeight = 15
   Next lr
End Sub

'AutoFits all rows in Excel Table
Sub AutoFitRows()
   Dim ws As Worksheet, lr As ListRow: Set ws = ActiveSheet
   For Each lr In ws.ListObjects(1).ListRows
    If Not (lr.Range.Cells.EntireRow.Hidden) Then lr.Range.Cells.EntireRow.AutoFit
   Next lr
End Sub

I added to my spreadsheet 2 buttons each connected to one of the above VBA Procedures. So conveniently now I can minimize my Excel rows or AutoFit all of them with just a single click like this: