DONT WANT TO "Rows to Repeat at top" on a condtion

[NimishK]

Hi Anyone

This could be quite challenging.

Have almost browsed through all the possible ideas of coding which i could not code of what i want but really wish to have great logic from you.

Kindly guide me so as to where to write the code in thisWorkbook or some other place

Basically I DONT WANT TO "Rows to Repeat at top" on a condtion.

Have attached the file for your reference.
https://www.dropbox.com/s/463fj7kv9a7uexo/Sample555-Sutures.xlsx?dl=0

here mydata Range refers to the items calculated from Row 15 onwards till the row which = "TOTAL" with additional Two rows and within column borders around

Kindly Print Preview
A. If you look at the file i have not coded but selected "Rows to Repeat at Top" = $14:$14 : Also scaled View to 85%

1. it comes correctly on Every Page
2. Now i would like to repeat the above condition with Coding
Condtion 1 : if mydata range i.e from row 15 to row 71 exceeds and falls into next page or pages in continuation then i want to "Rows to Repeat at Top " = $14:$14

Condition 2 : Suppose mydata range is from rows 15 to rows 57 with "TOTAL" at row 59 then I DONT WANT TO "Rows to Repeat at Top " = $14:$14
Eventhough datarange for Terms and Condtions have exceeded from 1st page onwards
Let me be also clear: Suppose if myData range exceeds more than 1 page or 2 pages then "Rows to Repeat " = $14:$14 but if the mydata Range ends in 2nd page then DONT WANT TO "Rows to Repeat" = $14:$14 on 3rd Page or more pages.

How to achieve the above result ?

Thanks
NimishK

 

[NimishK]

Thanks Worf and Indeed you are really taking pains
Sure it would have worked very well at your End Dear

Cheked in both the versions of Excel 2013 and 2007

In Excel 2013

If h = ws.HPageBreaks.Count Then
hgth = 2 * ws.HPageBreaks(h).Location.Row - ws.HPageBreaks(h - 1).Location.Row - 1
Else
hgth = ws.HPageBreaks(h + 1).Location.Row - 1
End If

I get error subscript out of Range what could be the reason

and in Excel 2007
Following Observations
Observation 1 : because with pp.PrintPreview Userform seen on Top and PrintPrview with Last Page behind Userform gets hanged and had to END TASK

when I added REM to pp.PrintPreview

'pp.PrintPreview
ActiveWindow.View = xlNormalView

Following Results as per the coding provided by you for Section(s) and Removing pp.PrintPreview

Section Case 1
ws.PageSetup.PrintTitleRows = ws.Rows(14).Address

Section Case 2
ws.PageSetup.PrintTitleRows = ""

It reads the Title Rows on First Page only and on Second, third Pages does not read

Now When i added REM in Case 2 before ws.PageSetup.PrintTitleRows = ""
i.e

'ws.PageSetup.PrintTitleRows = ""

Then it PrintTitleRow shows on all Pages
So it seems PrintTitleRows either Prints on all Pages or it does not Print on All Pages

but PrintTitleRows does not Show as per the condition

Set r = ActiveSheet.[b:b].Find("TOTAL", , xlValues)

Being Specific
i.e when we set the range B:B to find "TOTAL" then it should Display the PrintTitleRow on nos of Pages till "TOTAL" in Column B:B is found.
and Not to Display PrintTitleRow after "TOTAL" has been Searched and the Rows have exceeded with Data with nos. of Pages in that Sheet

Thanks
NimishK

 

[Worf]

Hi

• As I don’t have Excel 2007 anymore, we should concentrate on Excel 2013.
• I’m not using a user form for testing yet, instead running the code directly from the macro dialog box.
• Please run the code below and check if the result is correct. One possible explanation is that Excel is not in the proper view; the code is doing that, but to be sure, do it manually at View>Page break preview.
• I already can successfully preview the two sections separately with Excel 2013, so it should not be difficult to get this working for you.

Sub PBreaksInfo()
ActiveWindow.View = xlPageBreakPreview
MsgBox "#Horizontal page breaks: " & ActiveSheet.HPageBreaks.Count & vbLf & _
"#Vertical page breaks: " & ActiveSheet.VPageBreaks.Count
End Sub

 

[NimishK]

Worf


Ok the following works Perfect in Excel 2013 Yes seen two sections seperately

Sub PBreaksInfo()
ActiveWindow.View = xlPageBreakPreview
MsgBox "#Horizontal page breaks: " & ActiveSheet.HPageBreaks.Count & vbLf & _
"#Vertical page breaks: " & ActiveSheet.VPageBreaks.Count
End Sub

I am getting Error Subscript out of Range as per coding Post 10
in the procedure marked with RED

Dim pag() As Range, ws As Worksheet

Sub PrintPreviewPages()                                 ' run this one
Dim r As Range, cp%, tp%, i%, section%, pp As Range, j%
section = 1                                             ' with title
section = 2                                             ' no title
ActiveWindow.View = xlPageBreakPreview
PageAddress
Set r = ActiveSheet.[b:b].Find("TOTAL", , xlValues)
i = 1
Do While Intersect(pag(i), r) Is Nothing
    i = i + 1
    If i > 50 Then Exit Do
Loop
Select Case section
    Case 1
        Set pp = pag(1)
        For j = 2 To i
            Set pp = Union(pp, pag(j))
        Next
        ws.PageSetup.PrintTitleRows = ws.Rows(14).Address
    Case 2
        Set pp = pag(i + 1)
        For j = i + 2 To UBound(pag)
            Set pp = Union(pp, pag(j))
        Next
        ws.PageSetup.PrintTitleRows = ""
End Select
pp.PrintPreview
ActiveWindow.View = xlNormalView
End Sub


Sub PageAddress()
Dim c%, v%, h%, cln%, rw%, hgth%, wth%
Set ws = ActiveSheet
c = 1
ReDim pag(1 To (ws.VPageBreaks.Count + 1) * (ws.HPageBreaks.Count + 1))     'all pages on that sheet
For v = 0 To ws.VPageBreaks.Count
    For h = 0 To ws.HPageBreaks.Count
        If v = ws.VPageBreaks.Count Then
            If v = 1 Then
                wth = 2 * ws.VPageBreaks(1).Location.Column - 2
            Else
                wth = 2 * ws.VPageBreaks(v).Location.Column - ws.VPageBreaks(v - 1).Location.Column - 1
            End If
        Else
            wth = ws.VPageBreaks(v + 1).Location.Column - 1
        End If
        If h = ws.HPageBreaks.Count Then
[COLOR=#ff0000]            hgth = 2 * ws.HPageBreaks(h).Location.Row - ws.HPageBreaks(h - 1).Location.Row - 1[/COLOR]
        Else
            hgth = ws.HPageBreaks(h + 1).Location.Row - 1
        End If
        If v = 0 Then
            cln = 1
        Else
            cln = ws.VPageBreaks(v).Location.Column
        End If
        If h = 0 Then
            rw = 1
        Else
            rw = ws.HPageBreaks(h).Location.Row
        End If
        Set pag(c) = Range(Cells(rw, cln).Address & ":" & Cells(hgth, wth).Address)     ' page address
        c = c + 1
    Next
Next
End Sub

 

[Worf]

1) When the error happens, place the cursor over the “h” variable while the yellow error bar is still there. What value is displayed?

2) Valid indexes go from 1 to the number of horizontal page breaks. How many HPB do you actually have?

 

[NimishK]

Value of H = 1 with Yellow Bar Displayed

2) Valid indexes go from 1 to the number of horizontal page breaks. How many HPB do you actually have?

After the above Error and restarted all over again to PrntPrwv
Not sure but when Print Previewd it seems 2 and last 3rd showed Page empty

 

[Worf]

Hi

1) My code works with the active sheet, do you have the proper sheet active when running the code?
2) Page breaks are the dotted lines, see picture below, which is your workbook. That error line should only execute when h=2, hence without problems, assuming we are testing with the same file. Don’t forget to go to page break view before running it, just to be sure. It’s not guaranteed that it will work on normal view.
3) In short, I need to be sure on how many horizontal page breaks you have and if the code knows that.

 

[NimishK]

Worf

Yes it is the same file and works perfectly as per your image
I also PageBreakViewed it is as per your image
so by the way which is Horz.Pg break in blue dotted line am Confussed

Why this particular code cannot work on any other file if i have to use

In short, I need to be sure on how many horizontal page breaks you have and if the code knows that.

so there are 3 page breaks for 1st 3 pages and 4th page is totally blank as per the image and file
Is it necessary to know HPBreaks first and then code

1. what if my table(Range with PrintTitle row and borders) completes with "TOTAL" on 1st PAge and there is no data exceeded after"TOTAL". PgBreak = 1 and that is the End and it should not display page 2 as blank

2. What if my table(Range with PrintTitle row and borders) completes with "TOTAL" seen on 3rd PAge or 4thPAge and so on then PrintTitleRows till the Last "TOTAL" is seen if data exceeds within that page but after "TOTAL" i.e this could be the Last page and no more blank pages or More pages with data exceeded but without Printtitle rows

 

[Worf]

- Each dotted line is a page break. That image has two horizontal page breaks and one vertical page break.
- I can write generic code that will work on any file but first let’s solve your run time error.
- Likewise, I suggest we deal with blank pages after the error is gone.
- Right now I can’t see what causes your problem, so I propose that when you get the error, save the workbook without changing anything and post a link to it. Then, I’ll try to reproduce the error on my end. To ensure you are working with the correct sheet, it would be better to work with a single sheet workbook.

 

[NimishK]

Worf

Attaching Two Files one with Error and One Without Error
only have changed the file names
1. Following File with Error
https://www.dropbox.com/s/0xvxfun19ylyjnv/Sutures-Pageinfo-Error1.xlsm?dl=0

2. Following file without Error
https://www.dropbox.com/s/zmmjmt4b6kvzg3z/Sample555-PrintPreview555-Sutures-NoErrorsAt-All.xlsm?dl=0

The Error has gone completely as per attachment 2.
and Have added the folllowing on the userForm in attachment 2

Private Sub CmdPrint_Prev
Call PBreaksInfo
Call PrintPreviewPages
End sub

With the Mesasage i get Hoizontal Pages Breaks 2 and Vertical Pages break 1 as per above cmd

 

[Worf]

Hi Nimish

Here is what I did:

- Downloaded the file without errors. I would abandon the other one.
- Manually placed Excel in page break view.
- Executed the slightly modified code below directly from the macro dialog box.
- When choosing to preview the first section, I got two pages with titles, as expected. Note: a few times, it incorrectly displayed three pages.
This may be caused by switching views.
- When choosing the second section, four pages without titles were displayed, as expected.
- Please test it and define where to go from here.

Dim pag() As Range, ws As Worksheet


Sub PrintPreviewPages()                                 ' run this one
Dim r As Range, cp%, tp%, i%, section%, pp As Range, j%
section = Application.InputBox("Choose a section:", "1) with title" & " | " & "2) no title", "1", , , , , 1)
ActiveWindow.View = xlPageBreakPreview
PageAddress
Set r = ActiveSheet.[b:b].Find("TOTAL", , xlValues)
i = 1
Do While Intersect(pag(i), r) Is Nothing
    i = i + 1
    If i > 50 Then Exit Do
Loop
Select Case section
    Case 1
        Set pp = pag(1)
        For j = 2 To i
            Set pp = Union(pp, pag(j))
        Next
        ws.PageSetup.PrintTitleRows = ws.Rows(14).Address
    Case 2
        Set pp = pag(i + 1)
        For j = i + 2 To UBound(pag)
            Set pp = Union(pp, pag(j))
        Next
        ws.PageSetup.PrintTitleRows = ""
End Select
pp.PrintPreview
End Sub


Sub PageAddress()
Dim c%, v%, h%, cln%, rw%, hgth%, wth%
Set ws = ActiveSheet
c = 1
ReDim pag(1 To (ws.VPageBreaks.Count + 1) * (ws.HPageBreaks.Count + 1))     'all pages on that sheet
For v = 0 To ws.VPageBreaks.Count
    For h = 0 To ws.HPageBreaks.Count
        If v = ws.VPageBreaks.Count Then
            If v = 1 Then
                wth = 2 * ws.VPageBreaks(1).Location.Column - 2
            Else
                wth = 2 * ws.VPageBreaks(v).Location.Column - ws.VPageBreaks(v - 1).Location.Column - 1
            End If
        Else
            wth = ws.VPageBreaks(v + 1).Location.Column - 1
        End If
        If h = ws.HPageBreaks.Count Then
            hgth = 2 * ws.HPageBreaks(h).Location.Row - ws.HPageBreaks(h - 1).Location.Row - 1
        Else
            hgth = ws.HPageBreaks(h + 1).Location.Row - 1
        End If
        If v = 0 Then
            cln = 1
        Else
            cln = ws.VPageBreaks(v).Location.Column
        End If
        If h = 0 Then
            rw = 1
        Else
            rw = ws.HPageBreaks(h).Location.Row
        End If
        Set pag(c) = Range(Cells(rw, cln).Address & ":" & Cells(hgth, wth).Address)     ' page address
        c = c + 1
    Next
Next
End Sub