title: Zoom couple of columns to fit page with VBA
body: <p>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.</p>
  
  <p>My problem is to set the scale. What I'm doing:</p>
  
  <ul>
  <li>Take pagesize and translate to points, take off margin left and margin right = my printable area</li>
  <li>Get the width of range("B:E") = my range to fit the page</li>
  <li>Divide my printable area by my range to fit, multiply that with 100%, and extract 1% to make sure it will fit</li>
  </ul>
  
  <p>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...</p>
  
  <p>My code:</p>
  
  <pre><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 &lt;&gt; 0 And lngKolB &lt;&gt; 0 Then
          lngZoom = ((lngPagB / lngKolB) * 100) - 1
          With ActiveSheet.PageSetup
              .Zoom = lngZoom
          End With
      End If
  End If
  </code></pre>
  
  <p>Different widths:</p>
  
  <ul>
  <li>Column B: 45 (319 pixels) -> in Excel, set with VBA</li>
  <li>Column C: 15 (109 pixels) -> in Excel, set with VBA</li>
  <li>Column D: 30 (214 pixels) -> in Excel, set with VBA</li>
  <li>Column E: 20 (144 pixels) -> in Excel, set with VBA</li>
  <li>Column B-E: 589 points -> with VBA</li>
  <li>Page: 21 centimeters (595 points)</li>
  <li>Margins (left &amp; right): 1.8 centimeters (50.4 points)</li>
  <li>Print area: 595 - 101 (100.8) = 494 points</li>
  </ul>
  
  <p>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)...</p>