Adjusting Cell Size

This tutorial shows you how to adjust the size a cell's width and height in points.

The Chrt_in_cell.xls file gives an example of how to do set cell size in points and how to make a chart in a cell with VBA.

1. Excel Measurement Units

In an Excel worksheet, we can adjust the width of a column by dragging the boundary heading to the desired width. A ScreenTip displays the width in both number of standard font characters and pixels. In the example to the lower left, Column M is 8.6 standard font characters wide, equivalent to 48 pixels. We can also control the row height by dragging the row boundary to desired height. A ScreenTip shows the height in points and pixels. In the example to the lower right, Row 49 height is 20.25 points, 27 pixels.

2. Converting Between Characters, Pixels and Points

A point is 1/72nd of an inch. A pixel is 0.75 points. We can easily convert between points, pixels and inches, as shown in the following table.

Points	Pixels	Inches
18	24	0.25
36	48	0.5
72	96	1.00
108	144	1.5
144	192	2.0

Converting between points, pixels and inches is pretty simple. How do we handle column widths? We can convert pixels to column width in characters with the formula:

Column Width (Characters) = (Width Pixels - 5)/5

3. Setting Cell Width and Height with VBA

The VBA code below lets you input the desired cell width and height in points and then resizes the row and columns to the requested size of the active cell.

Public Sub adjust_cell_size()
' | Procedure to adjust active cell size to user's width and height requirements
' | D. Kelly O'Day, ProcessTrends.Com
' | 2/15/06
Dim cell_add As String
   On Error Resume Next                                                 ' In case user cancels
' Determine target cell
cell_add = ActiveCell.Address
' Ask user for cell size in points: width & height
   wide_pts = InputBox("Cell: " & cell_add & " Enter target width - points", "Target Width - Points ")
   height_pts = InputBox("Cell: " & cell_add & " Enter target height - points", "Target Height - Points")
' Calculate width in Pixels
     wide_pix = wide_pts * 1.3333333333333
' Calculate width in standard font characters
    wide_char = (wide_pix - 5) / 5
' Establish row and column
    what_row = ActiveCell.Row
    what_col = ActiveCell.Column
' Set column width and row height
    Columns(what_col).ColumnWidth = wide_char
    Rows(what_row).RowHeight = height_pts
End Sub

4. Getting Cell Size

Public Sub get_size_info()
'Procedure to return activecell width and height in points'
'D Kelly O'Day ProcessTrends.Com
Set rng = ActiveCell
Add = ActiveCell.Address
bsp;      Message = "Cell: " & Add & vbCrLf
         Message = Message & "Width = " & rng.Width & " Points" & vbCrLf
        Message = Message & "Height = " & rng.Height & " Points" & vbCrLf
out = MsgBox(Message, , "Cell Information")
End Sub