Description:

Some times we may enter the data into cells more than it’s width. In this case we can not able to see entire text. So we can change row height and Column width using excel using VBA. So that we can see entire data in that cell. When you have more lengthy data in cells, you can Auto Adjust Column Width or Row Height in Excel VBA to show the entire data. So that users can see the entire data in the cells. We will see with Examples.

Changing Row Height in Excel VBA

Change Row Height in Excel VBA
We can change row height in Excel using RowHeight Property of a Row in VBA. See the following example to do it.

Examples

The following example will change the height of the 3rd Row to 25.

Sub sbChangeRowHeight()

'Changing the 3rd row Height
Rows(3).RowHeight = 25

End Sub

We can also set the height for multiple rows, the following example will change the height of the 3rd to 20th row height to 25.

Sub sbChangeRowHeightMulti()

'Changing the 3rd-25the row Height
Rows("3:25").RowHeight = 25

End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute itit

Changing Column Width in Excel VBA

Change Column Width in Excel VBA
We can change column width in Excel using ColumnWidth Property of a Column in VBA. See the following example to do it.

In this Example I am changing the Column B width to 25.

Sub sbChangeColumnWidth()

Columns("B").ColumnWidth = 25

End Sub

Examples

We can also set the column width for multiple columns at a time, see this Example I am changing the Column B to E width to 25.

Sub sbChangeColumnWidthMulti()

Columns("B:E").ColumnWidth = 25

End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute it

Auto Adjust Column Width and Row Height using Excel VBA

Auto Adjust Column Width - Row Height in Excel VBA
We can use AutoFit method of Columns and Rows in Excel using VBA to Auto Adjust the rows and Columns.

Examples

Code to Auto Adjust Column Width

Following are the example to show you how to do this.

Sub sbAutoAdjustColumnWidth()

    Columns(2).AutoFit

End Sub
Code to Auto fit Row Height

Following are the example to show you how to do this.

   Sub sbAutoAdjustRowHight()
   Rows(2).AutoFit
End Sub
Instructions:

Follow the instructions below to test the codes above.

  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute it

Premium Project Management Templates

120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: May 18th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.