Print only visible rows.

[imback2nite]

Thank you for looking at this post. I have a user form that hides and shows different rows. I would like to print what is visible. Say I have rows 22 through 33 hidden. I would like to print 1 through 21 and 34 through 48. Excel is printing one through 48. Is there a way I can just print what is not hidden?

 

[imback2nite]

Hi,

if you insist on working on a filtered sheet it seems I'm out of this: my datasheet shows 127 sets of data (my setup brings up 2 pages wide) and I ended up with 216 pages being printed (as one pdf).

If I stick with my attempt to copy the relevant data over to a print sheet I end up with 8 pages (which is what I expected it to be) and it makes the work for the additional pagebreaks a lot easier:

Private Sub CommandButton7_Click()
' https://www.mrexcel.com/board/threads/print-only-visible-rows.1225399/
  Dim Response As Integer 'Print Revise'
  Dim wsPrint As Worksheet
  Dim wsData As Worksheet
  Dim lngNumVis As Long
 
  Const cstrShName As String = "HelpPrint"
 
  Response = MsgBox(prompt:="This action will print." & vbCr _
      & "      " & vbCr _
      & "                Continue?", Buttons:=vbYesNo + vbInformation)
  If Response <> vbYes Then Exit Sub
 
  Application.ScreenUpdating = False
 
  Set wsData = Worksheets("Payroll")
  If Evaluate("ISREF('" & cstrShName & "'!A1)") Then
    Set wsPrint = Worksheets("HelpPrint")
    wsPrint.UsedRange.Clear
  Else
    Set wsPrint = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    wsPrint.Name = cstrShName
  End If
  With wsData
    .Unprotect Password:="2"
    .Range("B1:Z" & wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy wsPrint.Range("A1")
    .Protect Password:="2"
  End With
  With wsPrint
    .ResetAllPageBreaks
    .Range("T2") = "REVISED"
    .UsedRange.Rows(1).Font.Bold = True
    lngNumVis = .Cells(.Rows.Count, "A").End(xlUp).Row
    With .PageSetup
      .PrintTitleRows = "$1:$9"
      .PrintTitleColumns = ""
      .PrintArea = wsData.UsedRange.Address
      .LeftHeader = "&T"
      .CenterHeader = "&""Tahoma,Bold""CONFIDE"
      .RightHeader = "&D"
      .CenterFooter = "&""Arial Narrow,Bold""&14" & Range("$M$2").Text
      .RightFooter = ""
      .LeftMargin = Application.InchesToPoints(0.1)
      .RightMargin = Application.InchesToPoints(0.1)
      .TopMargin = Application.InchesToPoints(0.5)
      .BottomMargin = Application.InchesToPoints(0.5)
      .HeaderMargin = Application.InchesToPoints(0.25)
      .FooterMargin = Application.InchesToPoints(0.25)
      .Orientation = xlLandscape
      .CenterHorizontally = True
      .Zoom = 64
      .Draft = False
    End With
    If lngNumVis > 42 Then .HPageBreaks.Add Before:=.Cells(43, 1)
    If lngNumVis > 74 Then .HPageBreaks.Add Before:=.Cells(77, 1)
    If lngNumVis > 106 Then .HPageBreaks.Add Before:=.Cells(107, 1)
    If lngNumVis > 130 Then .HPageBreaks.Add Before:=.Cells(131, 1)
    .Range("$A$1:$X$" & lngNumVis).PrintOut
    .ResetAllPageBreaks
    .UsedRange.ClearContents
  End With
  Application.Goto wsData.Range("B1"), True
  Set wsData = Nothing
  Set wsPrint = Nothing
  Unload UserForm7
  Application.ScreenUpdating = True

End Sub

Ciao,
Holger

Thank you! I'll look into making that work.

 

[Fluff]

Please do not mark a post as the solution when that post doesn't contain a solution. Thanks