Set Column Width and Row Height Based on Cell Value

vandango05

Board Regular
Joined
Oct 6, 2008
Messages
109
HI all. I've had a quick search but can't find exactly what i'm after. I wonder if someone can help me with some VBA to set column widths and row height based on values held within cells.

For example I would like to size column B based on a value in cell B2 and Row 3 based on a value in A3.

Thanks in advance.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
HI all. I've had a quick search but can't find exactly what i'm after. I wonder if someone can help me with some VBA to set column widths and row height based on values held within cells.

For example I would like to size column B based on a value in cell B2 and Row 3 based on a value in A3.

Thanks in advance.
Put this in a worksheet event module.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
    Columns("B").ColumnWidth = [B2]
End If
If Not Intersect(Target, Range("A3")) Is Nothing Then
    Rows(3).RowHeight = [A3]
End If
End Sub
 

vandango05

Board Regular
Joined
Oct 6, 2008
Messages
109
how can i change this to run with a button press rather than sheet change event? have changed from private to sub, but not sure what else i need to amend?
 

vandango05

Board Regular
Joined
Oct 6, 2008
Messages
109

ADVERTISEMENT

sorted it, just changing it to

Code:
Sub Size2()

Columns("f:f").ColumnWidth = Range("F3")
Columns("g:g").ColumnWidth = Range("g3")
   
End Sub

etc
 
Solution

Alex Oliveira

New Member
Joined
May 3, 2019
Messages
1
Put this in a worksheet event module.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
    Columns("B").ColumnWidth = [B2]
End If
If Not Intersect(Target, Range("A3")) Is Nothing Then
    Rows(3).RowHeight = [A3]
End If
End Sub



..........................................
Hi, there!

This code works for the same worksheet.

Please, what should I do to make this code get the ColumnWidth for a worksheet based on a cell value in another worksheet in the same workbook (the B2 value between brackets is in another worksheet)?

The same for the RowHeight. How do I refer to A3 in another worksheet?

And, the code should be in the in worksheet that I want to be changed or in the other one, where I would input the column width and row height?

Best regards,
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
HI all. I've had a quick search but can't find exactly what i'm after. I wonder if someone can help me with some VBA to set column widths and row height based on values held within cells.

For example I would like to size column B based on a value in cell B2 and Row 3 based on a value in A3.

Thanks in advance.

Try this.
  • In a blank row insert a value for column width that you want that row to be set to.
  • Name the range something. For example, "colwidth_range"
  • Create a sub that you can call using a button. I also have placed this code in the Private Sub Worksheet_Activate()
Here is the sub I use. To change the column widths, just change the value in the cell

VBA Code:
Private Sub Worksheet_Activate()
Dim colwidth_range as Range
Dim c as Range

Application.ScreenUpdating = False

Rows("1").EntireRow.Hidden = True ‘ Hide this row as it contains the column widths

' Reset column widths and row heights to autofit
ActiveSheet.Rows.AutoFit
ActiveSheet.Columns.AutoFit

' Set columnwidths to values held in each call in range
For Each c In Range("colwidth_range") 
     c.ColumnWidth = c.Value
Next c

End Sub

Regards,

Steve
 

Watch MrExcel Video

Forum statistics

Threads
1,122,601
Messages
5,597,117
Members
414,126
Latest member
jellevansoelen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top