VBA Page Layout Ruler

[Pinaceous]

Hi All,

I'm working with Excel's "Page Layout" Ruler in trying to set a column width for each pages print.

I notice that the Excel sheet for this Ruler page, if I adjust the column width manually upon the sheet, it represents the number as a fraction of an inch.

For example, upon the Excel sheet if manually adjust the Column width to 0.51" the vba code translates this as; Selection.ColumnWidth = 8.5

In running my code multiple times, I notice that it shrinks the columns size in adding extra columns onto the sheet, picture attached.

Can someone help me figure this out??

Thanks,
pinaceous

 

[Michael M]

Maybe this will help, but if you add extra columns, Excel WILL adjust the columns to suit, can't do anything about that, apart from removing surplus columns

• 
  Go to the Advanced section of your Excel Options (from the tabs on the left)
  
  
• Scroll to the Display section
  
  
• In “Ruler Units” drop-down, select the type of measurement you’d like
  
  
• Click OK when done

 

[Pinaceous]

Hey Michael M!

That helps a bit but how do I get Excel to not shrink the width? even when I'm telling it to stay at a certain number?

Thanks,
P!

 

[Michael M]

Either
Home>>Cells>>Format>>Column Width
OR
Maybe this tutorial

How to Lock Row Height & Column Width in Excel (Easy Trick)

It's quite easy to change a cell’s height and width in Excel.

trumpexcel.com

 

[Pinaceous]

Hi Michael M,

Thanks for getting back to me!

I appreciate your feedback but I'm having a problem, so here is my code:

Sub PrintOut()

Application.ScreenUpdating = True

Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = "&G"
        .CenterHeader = _
        "&8Title" & Chr(10) & "Title" & Chr(10) & "Title"
        .RightHeader = _
        "&8TERMINAL                             " & Chr(10) & "" & Chr(10) & "DATE                                     "
        .LeftFooter = "&6Form Revised on 04/21/2021"
        .CenterFooter = "&6Instructions"
        .RightFooter = "&6Instructions"
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.76)
        .BottomMargin = Application.InchesToPoints(0.7)
        .HeaderMargin = Application.InchesToPoints(0.25)
        .FooterMargin = Application.InchesToPoints(0.25)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 12
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = False
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
   
   
End Sub

With the picture I've uploaded, I'm noticing that Excel is adding extra columns on its own in not respecting the height and width that I'm setting for it.

Is there a way to adjust my code here to respect the following information:

    Columns("A:A").ColumnWidth = 0.03
    Columns("B:B").ColumnWidth = 2.5
    Columns("C:C").ColumnWidth = 7.7
    Columns("D:D").ColumnWidth = 7.6
    Columns("E:E").ColumnWidth = 7.6
    Columns("F:F").ColumnWidth = 9
    Columns("G:G").ColumnWidth = 9
    Columns("H:H").ColumnWidth = 8.5
    Columns("I:I").ColumnWidth = 8.9
    Columns("J:J").ColumnWidth = 7.5
    Columns("K:K").ColumnWidth = 7.7
    Columns("L:L").ColumnWidth = 7.5
    Columns("M:M").ColumnWidth = 8
    Columns("N:N").ColumnWidth = 7.5
    Columns("O:O").ColumnWidth = 7.3
    Columns("P:P").ColumnWidth = 6.5
    Columns("Q:Q").ColumnWidth = 6.5
    Columns("R:R").ColumnWidth = 6.5
    Columns("S:S").ColumnWidth = 7
    Columns("T:T").ColumnWidth = 5.5
    Columns("U:U").ColumnWidth = 5.9
    Columns("V:V").ColumnWidth = 5.5
    Columns("W:W").ColumnWidth = 5.5

   Rows("1:4").RowHeight = 13
    Rows("5:19").RowHeight = 43
    Rows("20:23").RowHeight = 13
    Rows("24:38").RowHeight = 43
    Rows("39:42").RowHeight = 13
    Rows("43:57").RowHeight = 42
    Rows("58:61").RowHeight = 13
    Rows("62:76").RowHeight = 42
    Rows("77:80").RowHeight = 13
    Rows("81:95").RowHeight = 42
    Rows("96:99").RowHeight = 13
    Rows("100:114").RowHeight = 42
    Rows("115:118").RowHeight = 13
    Rows("119:133").RowHeight = 42
    Rows("134:137").RowHeight = 13
    Rows("138:152").RowHeight = 42
    Rows("153:156").RowHeight = 13
    Rows("157:171").RowHeight = 42
    Rows("172:175").RowHeight = 13
    Rows("176:190").RowHeight = 42
    Rows("191:194").RowHeight = 13
    Rows("195:209").RowHeight = 42
    Rows("210:213").RowHeight = 13
    Rows("214:228").RowHeight = 42

What I'm trying to do is have Excel through VBA print out 12 pages where each of the smaller RowHeight's of 13 begins a new page at the top.

   Rows("1:4").RowHeight = 13

    Rows("20:23").RowHeight = 13

    Rows("39:42").RowHeight = 13

    Rows("58:61").RowHeight = 13

    Rows("77:80").RowHeight = 13

    Rows("96:99").RowHeight = 13

    Rows("115:118").RowHeight = 13

    Rows("134:137").RowHeight = 13

    Rows("153:156").RowHeight = 13

    Rows("172:175").RowHeight = 13

    Rows("191:194").RowHeight = 13

    Rows("210:213").RowHeight = 13

It seems like if I lock the sheet after I run my row/column height it appears to shrink the intended area and add columns off to its right in shrinking my pages.

Can you still help?

Thanks,
Pinaceous

 

[Michael M]

The only thing that I think is happening, is that the code is doing a fit to page

.FitToPagesWide = 1
 .FitToPagesTall = 12

It's a bit hard without the worksheet to play with,, I'm afraid

 

[Pinaceous]

Thanks Michael M for all of your valuable help!