Column width

[ctopher]

Is there a way to change column width to a certain inch and not font size? I am trying to create a table that has certain dimensions (inches), not font width.
thanks

 

[gearguru]

You can always print to "fit to 1 page"; doesn't it work for you?

 

[onlyadrafter]

Hello,

That would be brilliant if you could amend rows and columns to sizes instead of font width. Unfortunatley, this isn't available, not in V97 anyway.
However, if you enter a column width of 1, this equates to virtually 1/8" when printed at 100%. A Column width of 12.308 equates to 1" when printed at 100%. I'll leave any other calculations to yourself.




Hope this helps.

----------------------------------

maybe only a drafter
but the best user at this company!

 

[joerd]

The actual column width that you specify in Excel (via for example Format/Column/Width is: (quoting the Help) "The displayed column width is the average number of digits 0 through 9 of the standard font that fit in a cell.", where the standard font is the one you have specified in Tools/Options/General/Standard font, default set to Arial 10.
If you use VBA, it gets (a little) easier: then you specify the column width in Points (=1/72 of an inch), as in Worksheets(1).Columns(1).Width = 72 to specify a 1" column.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[joerd]

Actually, the column width you specify in Excel (by, for example, Format/Column/Width) is (quoting from Help) "The displayed column width is the average number of digits 0 through 9 of the standard font that fit in a cell." where the standard font is the one you specify in Tools/Options/General/Standard Font.
In VBA you specify the column width in points, which is 1/72 inch. So you can then set Worksheets(1).Columns(1).Width = 72 for a 1" column (although this may print differently dependent on page settings).

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[joerd]

Sorry for the duplicate post (and this one...;-)). The server seems to be generating random error messages.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[ctopher]

Good info. Thanks for all your help.

 

[MintJulep]

You could cut and paste to Word. Word lets you specify table column width in inches.

 

[Toolman59]

Lotus 123 Release 5 has exactly the feature you require.

Choose: View>Set View Preferences>Worksheet Frame, there is a drop down box with various settings, one of which is Inches

Toolman

 

[123121]

I have tried to set width to Millimeters. I tried to set column width to 10mm. I used following procedure:

1cm = 567 twips
1mm = 56.7 twips
1point = 20 twips

=> 1mm = 2.835 points

when I set up this value I NEVER get the result 10mm. I all the time got the value '9.789'. Can sombody help me out with hit issue. Seems to be similar to above one.

I think the main point is that Excel is rounging values by itself so in some cases it is not possible to sed an exact value (width/height)

Thanks for your help,
Vlado


Vlado

 

[onlyadrafter]

Hello,

Found this code in the Mrexcel forum to create a ruler on a spreadsheet, in cm or inches.

Sub MakeRuler_inch()

'Define the size of a new ruler.
Const Ruler_Width As Double = 6 'Width 6 inch
Const Ruler_Height As Double = 5 'Height 5 inch

'The setting size on the screen and the actual size on the printer.
Const Screen_Width As Double = 6
Const Screen_Height As Double = 5
Const Printer_Width As Double = 6
Const Printer_Height As Double = 5

Dim i As Long
Dim l As Double
Dim x As Long
Dim y As Long
Dim ws As Worksheet
Dim a(0 To 15) As Double
Dim x2 As Double
Dim y2 As Double

x = Ruler_Width * 16
y = Ruler_Height * 16
a(0) = 3.6: a(1) = 1: a(2) = 2: a(3) = 1: a(4) = 2: a(5) = 1: a(6) = 2: a(7) = 1
a(8) = 3: a(9) = 1: a(10) = 2: a(11) = 1: a(12) = 2: a(13) = 1: a(14) = 2: a(15) = 1
Application.ScreenUpdating = False

Set ws = ActiveSheet
Worksheets.Add
ActiveSheet.Move
ActiveSheet.Lines.Add 0, 0, 4.5 * x, 0
For i = 1 To x
l = a(i Mod 16)
ActiveSheet.Lines.Add 4.5 * i, 0, 4.5 * i, 4.5 * l
Next
ActiveSheet.Lines.Add 0, 0, 0, 4.5 * y
For i = 1 To y
l = a(i Mod 16)
ActiveSheet.Lines.Add 0, 4.5 * i, 4.5 * l, 4.5 * i
Next
ActiveSheet.Lines.Border.ColorIndex = 55

For i = 16 To x - 1 Step 16
With ActiveSheet.TextBoxes.Add(4.5 * i - 9, 4.5 * 3.6, 18, 12)
.Text = Format(i \ 16, "!@@&quot
End With
Next
For i = 16 To y - 1 Step 16
With ActiveSheet.TextBoxes.Add(4.5 * 3.6, 4.5 * i - 9, 12, 18)
.Orientation = xlDownward
.Text = Format(i \ 16, "!@@&quot
End With
Next
With ActiveSheet.TextBoxes
.Font.Size = 9
.Font.ColorIndex = 55
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Border.ColorIndex = xlNone
.Interior.ColorIndex = xlNone
End With

With ActiveSheet.DrawingObjects.Group
.Placement = xlFreeFloating
.Width = Application.InchesToPoints(x / 16)
.Height = Application.InchesToPoints(y / 16)
.CopyPicture xlScreen, xlPicture
ActiveSheet.Paste
x2 = (Selection.Width - .Width) / 3
y2 = (Selection.Height - .Height) / 3
Selection.Delete
.CopyPicture xlPrinter, xlPicture
ActiveSheet.Paste
.Width = .Width * .Width / (Selection.Width - x2 * 2) * Screen_Width / Printer_Width
.Height = .Height * .Height / (Selection.Height - y2 * 2) * Screen_Height / Printer_Height
Selection.Delete
If Val(Application.Version) >= 9 Then
.Copy
ActiveSheet.PasteSpecial 'Format:="Picture (PNG)"
With Selection.ShapeRange.PictureFormat
.CropLeft = x2
.CropTop = y2
.CropRight = x2
.CropBottom = y2
End With
Selection.Copy
ws.Activate
ws.PasteSpecial 'Format:="Picture (PNG)"
Selection.Placement = xlFreeFloating
.Parent.Parent.Close False
End If
End With
Application.ScreenUpdating = True
End Sub



'Ruler for Excel(Centimeter)

Sub MakeRuler_cm()

'Define the size of a new ruler.
Const Ruler_Width As Double = 16 'Width 16 cm
Const Ruler_Height As Double = 14 'Height 14 cm

'The setting size on the screen and the actual size on the printer.
Const Screen_Width As Double = 16
Const Screen_Height As Double = 14
Const Printer_Width As Double = 16
Const Printer_Height As Double = 14

Dim i As Long
Dim l As Long
Dim x As Long
Dim y As Long
Dim ws As Worksheet
Dim x2 As Double
Dim y2 As Double

x = Ruler_Width * 10
y = Ruler_Height * 10

Application.ScreenUpdating = False

Set ws = ActiveSheet
Worksheets.Add
ActiveSheet.Move
ActiveSheet.Lines.Add 0, 0, 3 * x, 0
For i = 1 To x
If i Mod 10 = 0 Then l = 5 Else: If i Mod 5 = 0 Then l = 4 Else l = 3
ActiveSheet.Lines.Add 3 * i, 0, 3 * i, 3 * l
Next
ActiveSheet.Lines.Add 0, 0, 0, 3 * y
For i = 1 To y
If i Mod 10 = 0 Then l = 5 Else: If i Mod 5 = 0 Then l = 4 Else l = 3
ActiveSheet.Lines.Add 0, 3 * i, 3 * l, 3 * i
Next
ActiveSheet.Lines.Border.ColorIndex = 55

For i = 10 To x - 1 Step 10
With ActiveSheet.TextBoxes.Add(3 * i - 9, 3 * 5, 18, 12)
.Text = Format(i \ 10, "!@@&quot
End With
Next
For i = 10 To y - 1 Step 10
With ActiveSheet.TextBoxes.Add(3 * 5, 3 * i - 9, 12, 18)
.Orientation = xlDownward
.Text = Format(i \ 10, "!@@&quot
End With
Next
With ActiveSheet.TextBoxes
.Font.Size = 9
.Font.ColorIndex = 55
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Border.ColorIndex = xlNone
.Interior.ColorIndex = xlNone
End With

With ActiveSheet.DrawingObjects.Group
.Placement = xlFreeFloating
.Width = Application.CentimetersToPoints(x / 10)
.Height = Application.CentimetersToPoints(y / 10)
.CopyPicture xlScreen, xlPicture
ActiveSheet.Paste
x2 = (Selection.Width - .Width) / 3
y2 = (Selection.Height - .Height) / 3
Selection.Delete
.CopyPicture xlPrinter, xlPicture
ActiveSheet.Paste
.Width = .Width * .Width / (Selection.Width - x2 * 2) * Screen_Width / Printer_Width
.Height = .Height * .Height / (Selection.Height - y2 * 2) * Screen_Height / Printer_Height
Selection.Delete
If Val(Application.Version) >= 9 Then
.Copy
ActiveSheet.PasteSpecial 'Format:="Picture (PNG)"
With Selection.ShapeRange.PictureFormat
.CropLeft = x2
.CropTop = y2
.CropRight = x2
.CropBottom = y2
End With
Selection.Copy
ws.Activate
ws.PasteSpecial 'Format:="Picture (PNG)"
Selection.Placement = xlFreeFloating
.Parent.Parent.Close False
End If
End With
Application.ScreenUpdating = True
End Sub




Hope this helps.

----------------------------------

maybe only a drafter
but the best user at this company!

 

[Grunchy]

OK I found out some info regarding column widths and row heights in Excel 2000, this is after extensive experimentation so it may be of worth to anyone else printing from Excel.

First of all, I found out that the minimum margin size is 1/4". If I try to set any margin to less than 1/4", it reverts to a minimum of 1/4". This could be dependent on the print driver so your results may vary.

Next, I found that using a factor of 72 points = 1" does not give exact measurements in Excel 2000. In fact, after trying it out and making a bunch of print-outs, it seems that Excel uses 74 points = 1" vertical, and 70 points = 1" horizontal.

Another thing I found out: you cannot use .Width in visual basic to set column width in points, because .Width is read-only. You still need to set width with .ColumnWidth to the number of characters.

Furthermore, .ColumnWidth sets the width of the column according to the width of the zero "0" character in the default font, which is arial size 10. So if you execute:

Worksheets(1).Columns(1).ColumnWidth = 10 'ten 0's wide
Width = (Worksheets(1).Columns(1).Width / 10) 'width of 0 in points

I got Width = 5.625 points wide, which would be the width of the "0" character.

Another thing: if you set width with .ColumnWidth, and it isn't a whole number, it rounds to the nearest 7th of a character. So if you set column width to 9.8 characters, it'll actually be set to 9.86 characters (9 6/7 characters). Go ahead and try it.

Here is a VBA program I created to print onto a sheet of Avery 5660 labels (3 across by 11 tall, or 2.833" wide by 1" tall). I set the print area to be cells A1:F11 and the print scaling adjustment to 100%. The worksheet name is "Labels".


Const PointsVert As Integer = 74
Const PointsHoriz As Integer = 70
Dim i As Integer
Dim Width As Double

With Worksheets("Labels&quot
'set margins
.PageSetup.LeftMargin = 0.25 * PointsHoriz ' 0.25"
.PageSetup.RightMargin = 0.25 * PointsHoriz
.PageSetup.TopMargin = 0.25 * PointsVert
.PageSetup.BottomMargin = 0.25 * PointsVert

'set row heights
For i = 2 To 10
.Rows(i).RowHeight = 1 * PointsVert ' 1"
Next i
.Rows(1).RowHeight = 0.75 * PointsVert
.Rows(11).RowHeight = 0.75 * PointsVert

'set column widths, first calculate width of "0" in standard font
.Columns(1).ColumnWidth = 10
Width = (.Columns(1).Width / 10) / PointsHoriz
.Columns(1).ColumnWidth = 0.75 / Width
.Columns(2).ColumnWidth = (8.5 / 3 - 1) / Width
.Columns(3).ColumnWidth = 1 / Width
.Columns(4).ColumnWidth = .Columns(2).ColumnWidth
.Columns(5).ColumnWidth = .Columns(3).ColumnWidth
.Columns(6).ColumnWidth = (8.5 / 3 - 1.25) / Width
End With


After you run the program, try "print preview". The contents of the print range A1:F11 should print perfectly onto Avery 5660 labels.

All the best,
Grunchy