Xtykutl

I'm having trouble fitting my columns in Excel on a sheet.
I have a sheet with columns from A to CK (can be different per project).
I don't need to print column A, but column B has to be on all pages and next to column B has to be 3 columns. So that will make column "B,C:E" on first page, next page "B,F:H", and so on... Column B is set as title, so it will be printed on every page.

My problem is to set the scale. What I'm doing:

• Take pagesize and translate to points, take off margin left and margin right = my printable area


• Get the width of range("B:E") = my range to fit the page


• Divide my printable area by my range to fit, multiply that with 100%, and extract 1% to make sure it will fit

The outcome in my situation is 83, but is has to be 77 to fit the page. I'll have to find other numbers I think, but I don't know how and which...

My code:

If ActiveSheet.Name = "Meterkastlijst" Then

    Dim lngZoom As Long

    Dim lngKolB As Long

    Dim lngPagB As Long

    lngKolB = ActiveSheet.Range("B:E").Width

    If ActiveSheet.PageSetup.PaperSize = xlPaperA4 Then

        lngPagB = CLng(Application.CentimetersToPoints(21)) - CLng((ActiveSheet.PageSetup.LeftMargin + ActiveSheet.PageSetup.RightMargin))

    ElseIf ActiveSheet.PageSetup.PaperSize = xlPaperA3 Then

        lngPagB = CLng(Application.CentimetersToPoints(29.7)) - CLng((ActiveSheet.PageSetup.LeftMargin + ActiveSheet.PageSetup.RightMargin))

    End If

    If lngPagB <> 0 And lngKolB <> 0 Then

        lngZoom = ((lngPagB / lngKolB) * 100) - 1

        With ActiveSheet.PageSetup

            .Zoom = lngZoom

        End With

    End If

End If

Different widths:

• Column B: 45 (319 pixels) -> in Excel, set with VBA


• Column C: 15 (109 pixels) -> in Excel, set with VBA


• Column D: 30 (214 pixels) -> in Excel, set with VBA


• Column E: 20 (144 pixels) -> in Excel, set with VBA


• Column B-E: 589 points -> with VBA


• Page: 21 centimeters (595 points)


• Margins (left & right): 1.8 centimeters (50.4 points)


• Print area: 595 - 101 (100.8) = 494 points

With numbers above it calculates 83%, but then it doesn't fit, when I set it manually to 77% it does fit, but how can I get this number with VBA? I don't understand the column widths, what I see in Excel and how I set it in VBA (45+15+30+20) is different from what VBA tells me it should be (589)...