Select visible range and add page break after every 20th visible cell

I currenty have a sheet that I want to add page breaks after every 20th visible cell in a specified range. On the sheet there are hidden and visible cells so I need only the visible cells selected in the range. I have been unable to find anything on this specific topic.

That unfortnately does not work. To specify further details... sheet24.range("D7:O109") is the sheet and range I will use. I think I may have to use HPageBreaks.Add in order to not ruin my conditional formating. Thank you for your quick responce!

Oh you meant you want like a real page break for printing the information out?

Exactly. I just have been having issues due to cells being hidden in the selected cell ranges.

Here, this worked in a quick test I ran. Just change the range.

Please Login or Register  to view this content.

That seemed to work slightly better but still not right. I need the print area to always stay at one page. My columns are larger then the standard one page so the print preview screen is cutting them out. Also, the macro seems to be adding a page break after every cell in the visible range. Thank you for all your help with this.

Dim i As Integer, j As Integer
Dim wsh As Worksheet
Set wsh = Sheet13

'remove all page breaks
wsh.ResetAllPageBreaks
'enable autoajusting
wsh.PageSetup.Zoom = False
'set new area
wsh.PageSetup.PrintArea = "C7:G107"
'set paper orientation
wsh.PageSetup.Orientation = xlLandscape
'add horizontal page break on every 5 row
For i = 1 To 5 Step 1
j = j + 1
wsh.HPageBreaks.Add wsh.Range("A" & i + 1)
Next i


wsh.PageSetup.FitToPagesWide = 1
wsh.PageSetup.FitToPagesTall = j

Set wsh = Nothing

End Sub

I was using something like this before but it was counting my hidden cells.

Dim i As Integer, j As Integer
Dim wsh As Worksheet
Set wsh = Sheet13

'remove all page breaks
wsh.ResetAllPageBreaks
'enable autoajusting
wsh.PageSetup.Zoom = False
'set new area
wsh.PageSetup.PrintArea = "C7:G107"
'set paper orientation
wsh.PageSetup.Orientation = xlLandscape
'add horizontal page break on every 5 row
For i = 1 To 5 Step 1
j = j + 1
wsh.HPageBreaks.Add wsh.Range("A" & i + 1)
Next i


wsh.PageSetup.FitToPagesWide = 1
wsh.PageSetup.FitToPagesTall = j

Set wsh = Nothing

End Sub

I was using something like this before but it was not counting my hidden cells.