Clear & Reset Page Breaks On Visible Rows Only for Printing & Ignore Hidden Rows

[bearwires]

I have a document which has 76 pages.

All 76 pages are on 1 worksheet called "COSHH"

Each page is made up of a standard template form, same number of rows & columns for each page.

Each form page is blank unless validation drop down lists on other worksheets are selected which then populates the respective form/rows using IF function, pulling data from a completely separate worksheet.

Each validation drop down cell on each of the other worksheets is linked to its own template form/page.

If a drop down item is selected, data is pulled from the other worksheets to populate the respective form in column B of the COSHH worksheet.

Column A cells have the form headings and is blank "" unless column B is populated using IF function.

I have a macro which hides all blank rows (forms) if they have no data. This macro works great, thanks to JohnnyL here on MrExcel.


The problem is, when I go to print preview it is showing all 76 pages, most of which are blank even though they are hidden on the worksheet and not visible in print layout / normal layout / etc.

I have tried clearing all page breaks and setting them again just in case that was an issue, but when I hide all the blank rows, it stills shows all 76 pages in the print preview so it didnt work.


If I hide all the empty forms / rows, then clear all page breaks, then reset the page breaks for just the cells that are not hidden, this will work and it will only show the forms I need to see in the print preview, which is what I want the end result to be.


Is there a way to use some VBA code to clear & reset the page breaks, then insert new page breaks on the non-hidden rows?
The hiding part is sorted, it is just the resetting & re-inserting of new page breaks to rows that are not hidden I am looking to achieve.

Page 1 starts at row 5

Row 5 - Row 33 is Page 1
Row 34 - Row 62 is Page 2
Row 63 - Row 91 is Page 3
Row 92 - Row 120 is Page 4
ETC

Cells B32, B61, B90, B119.......B2207 will contain a text string if the page is to be visible / printed. These same cells will be "" blank if the page is hidden from view / not to be printed.

 

[bearwires]

I have a document which has 76 pages.

All 76 pages are on 1 worksheet called "COSHH"

Each page is made up of a standard template form, same number of rows & columns for each page.

Each form page is blank unless validation drop down lists on other worksheets are selected which then populates the respective form/rows using IF function, pulling data from a completely separate worksheet.

Each validation drop down cell on each of the other worksheets is linked to its own template form/page.

If a drop down item is selected, data is pulled from the other worksheets to populate the respective form in column B of the COSHH worksheet.

Column A cells have the form headings and is blank "" unless column B is populated using IF function.

I have a macro which hides all blank rows (forms) if they have no data. This macro works great, thanks to JohnnyL here on MrExcel.


The problem is, when I go to print preview it is showing all 76 pages, most of which are blank even though they are hidden on the worksheet and not visible in print layout / normal layout / etc.

I have tried clearing all page breaks and setting them again just in case that was an issue, but when I hide all the blank rows, it stills shows all 76 pages in the print preview so it didnt work.


If I hide all the empty forms / rows, then clear all page breaks, then reset the page breaks for just the cells that are not hidden, this will work and it will only show the forms I need to see in the print preview, which is what I want the end result to be.


Is there a way to use some VBA code to clear & reset the page breaks, then insert new page breaks on the non-hidden rows?
The hiding part is sorted, it is just the resetting & re-inserting of new page breaks to rows that are not hidden I am looking to achieve.

Page 1 starts at row 5

Row 5 - Row 33 is Page 1
Row 34 - Row 62 is Page 2
Row 63 - Row 91 is Page 3
Row 92 - Row 120 is Page 4
ETC

Cells B32, B61, B90, B119.......B2207 will contain a text string if the page is to be visible / printed. These same cells will be "" blank if the page is hidden from view / not to be printed.

With alot of googling and trial & error, I managed to code something which achieves what I wanted.
Here is the code just in case it can help someone else:

Sub PageBreaksToVisibleRows()
'
lr As Long, k As Long
  Dim Cell As Range
  
  Const RowsPerPageBreak As Long = [COLOR=rgb(226, 80, 65)]29[/COLOR]
  
  lr = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  With Sheets("[COLOR=rgb(226, 80, 65)]Sheet 1[/COLOR]").ResetAllPageBreaks
  
For Each Cell In Range("A[COLOR=rgb(226, 80, 65)]5[/COLOR]:A" & lr).SpecialCells(xlVisible)
    k = k + 1
    If k = RowsPerPageBreak + 1 Then
      ActiveSheet.HPageBreaks.Add Before:=Cell
      k = 1
    
End If
  Next Cell
End With

End Sub

Just amend the red items to suit your requirements.