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

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!

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.

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.

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.

Good info. Thanks for all your help.

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

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

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

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, "!@@")
        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, "!@@")
        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, "!@@")
        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, "!@@")
        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!

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")
    '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