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


delete blank cells in excelcell background color vbars getrowsvba print statementarrays in vbaexcel surface chartsas language basicsvba switch statementunprotect protected excel sheethow to calculate difference between dates in excelhow to remove duplicate cells in excel 2010task excel templateunlock excel filems excel logical operatorscheckbox vba excelexcel formula vbamerging worksheets in excelmessage box vba accessexcel shortcut insert rowexcel cells vbamacro filter excelgantt chart excel 2007 templatewhat is the vlookup function in excelresidential construction schedule template excelstatistical chart typesvba delete cell contentsexcel vba open filevba cells valueexcel concatenationexcel 2007 developer tab addhow to dedupe in excel 2010excel vba messagesql pivot table examplespivot table charts excel 2010command button vbaexcel eliminate duplicatesexcel vba vlookup functionhow to unlock an excel spreadsheetexcel vba language referenceexcel graph wizardexcel vba print formexcel vba messageboxvba loops for dummiesforgot unprotect sheet passwordquick keys in excelmacro excel insert rowshort cut key excelexcel template for project trackingvba function submacros not working in excel 2013undo vbaexcel protect sheet password removersum vlookup excelhow to clear formatting in excelactivex control excelhow to learn macros in excelview hidden columns in excelbusiness analyst tutorial pdfvba sql accesshow to unprotect excelexcel macros tutorial pdfvba set cell colorvba nullexcel vba hide columnswot excel templateproject timeline template excel 2007what is a pivot table in excel 2010how to write a vlookup formula in excelsorting vbaexcel macro protect sheet with passwordexcel macro copyworkbook and worksheetaccess vba referencevba samplehow to use if formula in excel 2007vba print functionend xlup vbaproject dashboard excel templateproject schedule xls