Change Row Height and Column Width using Excel VBA

Home/Excel VBA/Change Row Height and Column Width using Excel VBA

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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
LIMITED TIME OFFER
By |May 18th, 2013|Excel VBA|4 Comments

About the Author:

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.

4 Comments

  1. Koert penne March 29, 2016 at 2:35 PM - Reply

    I wanted the row height for content of one column, regardsless what was in the other columns. I did it as follows:

    Sub rowheight_one_column()

    Column = InputBox(“Hoeveelste kolom?”) + 0

    Rows(20).Delete

    For x = 4 To 13
    Cells(20, Column) = Cells(x, Column)
    Rows(20).AutoFit
    hoogte = Cells(20, Column).RowHeight
    Rows(x).RowHeight = hoogte
    Next x

    Rows(20).Delete

    End Sub

  2. Sathish October 26, 2016 at 11:39 AM - Reply

    Please help me for auto fit the entire sheet1

  3. Jack February 21, 2017 at 7:43 PM - Reply

    where have you declared your Variables, It does confuse people when your code is not neat and is exposed on the Internet where everybody does search and get stuck.

  4. dskar April 13, 2017 at 7:10 AM - Reply

    the difference between writing the code in code window and a module?

Leave A Comment


Related pages


hide excel worksheetexcel macro arrayvba active sheetedit macro in excelunhiding cells in excelhow to collect data from multiple excel filesexcel vba macro examplesaccess msgboxvba code for vlookupexcel vba copy rowhow to create pivot in excelvb syntaxexcel macro rangeeasy excel vbaremove dupes in excelexcel transfer data between worksheetshow to delete a row in excel using vbatcl commands in sqlvba excel copy rangevb opentextfilepowerpivot excel 2010 tutorialhow to delete a header in excelrange select vbaextract hyperlink excelaverageif formulavba saveascommand button in excel 2007dashboard reporting templatesformula for lookup in exceladodb connection openexcel vba serieslooking for duplicates in excelinsert row in excelhow do i remove hyperlinks in excelexcel mysql odbcadvanced filtering excel 2013auto column width excelmicrosoft excel alphabetical orderexcel vba propertyhow to change all column widths in exceldml in databasestart vba in exceltick mark in excel 2007excel based dashboardshide cells in excelpivot tables tutorialsexcel checkbox in cellms excel hlookup examplevlookup and hlookup in excel 2007vba access excelexcel 2010 delete duplicatescoding macros in excelmacro programming in excelvba excel 2007 tutorialrun excel macro on openexcel 2010 password remover macrodefine vlookuptutorial vba excelsql syntaxesvba excel averageworksheetfunction.vlookupsum function vbahow to do vlookup in excel 2007how to merge columns in excel 2007how to activate developer tab in excel 2010password protect a spreadsheetvba case orexcel macro hotkeyexample of vlookup function in excel 2010delete blank cells in excelexcel button for macrocheck box excelvba integer rangefilescriptingobjectname range excel 2010else if statement vbagantt chart excel 2007statistical chart types