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?

 

[HaHoBe]

Hi,

instead of

Range("A1:H48").PrintOut

try using

Range("A1:H48").SpecialCells(xlCellTypeVisible).PrintOut

Ciao,
Holger

 

[imback2nite]

Thank you!
I tried with this code and it works! But now it prints the rows where they would be if the hidden rows were actually there!
Say I want to print rows 43-45 and then rows 74-77. These rows DO print but on pages 3 and 6. Just a bunch of blank area between. The space for the rest of the hidden rows are still being utilized, but blank where it would go. Strange? Is that normal?

Private Sub CommandButton7_Click()
    Dim Response As Integer 'Print Revise'
    Response = MsgBox(prompt:="This action will print." & vbCr _
    & "      " & vbCr _
    & "                Continue?", Buttons:=vbYesNo + vbInformation)
    If Response <> vbYes Then Exit Sub
    Application.ScreenUpdating = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$9"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.Unprotect Password:="4wink"
    Range("T2") = "REVISED"
    ActiveSheet.Protect Password:="4wink"
    ActiveSheet.PageSetup.PrintArea = "$B$1:$Z$129"
    With ActiveSheet.PageSetup
        .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
    Range("$B$1:$Z$129").SpecialCells(xlCellTypeVisible).PrintOut
    ActiveSheet.Unprotect Password:="2"
    Range("T2") = ""
    ActiveSheet.Protect Password:="2"
    Range("B1").Select
    Unload UserForm7
    Application.ScreenUpdating = True
End Sub

 

[kevin9999]

Try changing

ActiveSheet.PageSetup.PrintArea = "$B$1:$Z$129"

To

ActiveSheet.PageSetup.PrintArea = ActiveSheet.Range("B1:Z129").Rows.Address

when you have the rows hidden, then just

ActiveSheet.PrintOut

when you're ready to print.

 

[HaHoBe]

Hi,

you unprotect/protect the activesheet two times using different passwords?

As I don't know what version of Excel you use for this I can only point out that in my version (Excel2019) SpecialCells won't work on a protected sheet. And Excel2019 prints the visible area by default.

Holger

 

[HaHoBe]

Hi,

using this modified code which copies the visible area over to a new added sheet, uses this for printout and deletes after print might help:

Private Sub CommandButton7_Click()
  Dim Response As Integer 'Print Revise'
  Dim wsPrint As Worksheet
  Dim wsData As Worksheet
 
  Const cstrShName As String = "HelpPrint"
  Const cstrData As String = "DataConsolidation"     'change name of sheet for Dara to suit
  Const cstrPW As String = "4wink"
 
  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(cstrData)
  If Not Evaluate("ISREF('" & cstrShName & "'!A1)") Then
    Set wsPrint = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    wsPrint.Name = cstrShName
  End If
  With wsData
    .Unprotect Password:=cstrPW
    .Range("B1:Z129").SpecialCells(xlCellTypeVisible).Copy wsPrint.Range("A1")
    .Protect Password:=cstrPW
  End With
  With wsPrint
    .Unprotect Password:=cstrPW
    .Range("T2") = "REVISED"
    With .PageSetup
      .PrintTitleRows = "$1:$9"
      .PrintTitleColumns = ""
      .PrintArea = "$A$1:$Y$" & wsPrint.Cells(wsPrint.Rows.Count, "A").End(xlUp).Row
      .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
    .UsedRange.PrintOut
    .Range("T2") = ""
    .Protect Password:=cstrPW
  End With
  Application.Goto wsData.Range("B1"), True
  Set wsData = Nothing
  Application.DisplayAlerts = False
  wsPrint.Delete
  Application.DisplayAlerts = True
  Set wsPrint = Nothing
  Unload UserForm7
  Application.ScreenUpdating = True

End Sub

Ciao,
Holger

 

[imback2nite]

Sorry about the delay. First Christmas with my wife. Anyway, I've been messing around with this and it does what I want but now I just seem to have a problem with the pagebreaks. I'm using this code but the print area will always be different. On page one there is no problem if there are less that eight areas or 42 lines that need printing. Any more than that I will need manual pagebreaks. Is there a way I can use the IF statement? For example, IF the print needs to go beyond 42 lines I would like to set a page break at 42 lines. Then IF it goes beyond 74 lines to set a page break at 74 lines. And again and 106 and 130. The print will not go beyond 130 lines. Again thank you for all your help!

 Worksheets("Payroll").Rows(42, 74, 106, 130).PageBreak = xlPageBreakManual

Private Sub CommandButton7_Click()
Application.ScreenUpdating = False 'Print Payroll Revise'
ActiveSheet.Unprotect Password:="2"
With Worksheets("Payroll")
.ResetAllPageBreaks
End With
Selection.Font.Bold = True
Range("T2") = "REVISED"
Columns("AA:AA").EntireColumn.Hidden = True
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$9"
.PrintTitleColumns = ""
.LeftHeader = "&T"
.CenterHeader = "&""Tahoma,Bold""CONFIDENTIAL : PAYROLL REVISE"
.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
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Columns("AA:AA").EntireColumn.Hidden = False
Range("A1").Select
ActiveSheet.Protect Password:="2"
Unload UserForm7
Application.ScreenUpdating = True
End Sub

 

[HaHoBe]

Hi there,

1. is the range on the sheet you want to printout filtered or is it a continuous range?
2. on the opening code the columns were limited to B to Z, now you hide Column AA. Could you tell us about the columns for printout?
3. the last code presented shows Selection.Font.Bold = True which will bold whatever is selected in the sheet, you should specify that by assigning a range like
   ActiveSheet.UsedRange.Rows(1).Font.Bold = True
4. Columns("AA:AA").Hidden = True should be enough to do the job

Ciao,
Holger

 

[imback2nite]

Hi there,

1. is the range on the sheet you want to printout filtered or is it a continuous range?
2. on the opening code the columns were limited to B to Z, now you hide Column AA. Could you tell us about the columns for printout?
3. the last code presented shows Selection.Font.Bold = True which will bold whatever is selected in the sheet, you should specify that by assigning a range like
   ActiveSheet.UsedRange.Rows(1).Font.Bold = True
4. Columns("AA:AA").Hidden = True should be enough to do the job

Ciao,
Holger

Great questions! Let's see.
1. I have a user form that will allow the user to select which rows will be reprinted so the range will always be columns (B:Z) but the rows will most certainly not be contagious. Like rows 10:14 and 16:20 and then 40:60 will need printing. So what I've done is hide the rows in between. My problem is that Excel prints the selected rows and empty areas in between so I needed to manually reset all page breaks.
2. Excel also prints column AA so I hid it during printing and unhid it afterwards.
3. You're right. But I'm still working out how I need the font to print.

 

[HaHoBe]

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