Set Column Width and Row Height Based on Cell Value

[vandango05]

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.

 

[vandango05]

Ideally i'd like to set them to cm

 

[JoeMo]

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.

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]

Thanks, works well.

 

[JoeMo]

Thanks, works well.

You are welcome - thanks for the reply.

 

[vandango05]

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]

sorted it, just changing it to

Sub Size2()

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

etc

 

[Alex Oliveira]

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.

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]

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

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