Print Preview Visible Columns

[howard]

I have code below to see the print Preview. I need the code amended so as to display only the range of the unhidden columns i.e visible data

When running the macro, the preview shows the hidden columns , which I do not want to see

for eg if Unhidden Range is A:B and I:J, then I only want to print A2 to last row in Col B and I2 to last row in Col J

It would be appreciated if someone could kindly amend my code

 Sub Print_Preview()
Sheets("Summary").Select
Range("A1").Select
  LR = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Summary").Select
 With ActiveSheet.PageSetup
.PrintGridlines = True
.PrintArea = "A2:AC" & LR + 5
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
.LeftHeader = "&D&T"
.CenterHeader = "SVC and Parts Turnover"
.Orientation = xlLandscape
.FitToPagesWide = 1
End With

Sheets("Summary").Select

ActiveWindow.View = xlPageBreakPreview
ActiveSheet.UsedRange.EntireColumn.AutoFit
      Range("A1").Select
      
End Sub

 

[HaHoBe]

Hi howard,

how about

Sub Print_Preview_mod()
' https://www.mrexcel.com/board/threads/print-preview-visible-columns.1225003/
Dim lr As Long
With Sheets("Summary")
  lr = .Cells(Rows.Count, "A").End(xlUp).Row
  With .PageSetup
    .PrintGridlines = True
    .PrintArea = "A2:AC" & lr + 5
    .PrintTitleRows = "$1:$1"
    .PrintTitleColumns = ""
    .LeftHeader = "&D&T"
    .CenterHeader = "SVC and Parts Turnover"
    .Orientation = xlLandscape
    .FitToPagesWide = 1
  End With
  .UsedRange.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit

  ActiveWindow.View = xlPageBreakPreview
End With
End Sub

Ciao,
Holger

 

[howard]

Many thanks Holger

I need you to make one change that I did not think abount before posting

I want to print preview onl;y visible columns that contains data in row1 for eg if the unhidden columns are A:B and Col F onwards and there is data in A1:B1 and F1:J1, only those columns must display in the page preview up to the last row conmtaining data in Col A


Kindly amend your code accordingly

 

[HaHoBe]

Hi howard,

like this

Sub Print_Preview_mod02()
' https://www.mrexcel.com/board/threads/print-preview-visible-columns.1225003/
Dim lr As Long
Dim rngCell As Range

With Sheets("Summary")
  lr = .Cells(Rows.Count, "A").End(xlUp).Row
  With .PageSetup
    .PrintGridlines = True
    .PrintArea = "A2:AC" & lr + 5
    .PrintTitleRows = "$1:$1"
    .PrintTitleColumns = ""
    .LeftHeader = "&D&T"
    .CenterHeader = "SVC and Parts Turnover"
    .Orientation = xlLandscape
    .FitToPagesWide = 1
  End With
  For Each rngCell In .Range("A1:AC1")
    If Trim(rngCell) = vbNullString Then rngCell.EntireColumn.Hidden = True
  Next rngCell
  .UsedRange.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit
  ActiveWindow.View = xlPageBreakPreview
End With
End Sub

Ciao,
Holger

 

[howard]

Many thanks Holder

Your code works perfectly