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

 

[Worf]

1) Formatting cell borders will produce a visually identical result, if you don’t need merged cells for lengthy text. This could simplify the coding.

2) If you want to proceed with merged cells, please post once more the problematic workbook, since I didn’t get errors when testing.
Are you on Excel 2007 or 2013?

 

[NimishK]

1) Formatting cell borders will produce a visually identical result, if you don’t need merged cells for lengthy text. This could simplify the coding.

See the above image : A) Will i get exact Center position like the Merged Cell Centre point if i format the cells
B) Please observe the Center point between the Two images of same coloumns compared with merged cells and without merged cells. There is a Slight-Difference

File Attached https://www.dropbox.com/s/5ka0l5s50aqk8ty/5045.xlsm?dl=0
Below run time error when ran the above file

ws.HPageBreaks(j - 1).Delete

am using both 2007 and 2013 till today the checking have done from 2007
Have to test the same in 2013

 

[NimishK]

Sorry i could not paste the image for above post 42

PFA https://www.dropbox.com/s/evye3btbur6iy03/Merged-no-Merged-cells.jpg?dl=0

 

[Worf]

Hi

1. I’m on Excel 2016 today and was not able to reproduce the error with the latest workbook. I ran the macro both directly and via the user form and it print previewed correctly. See the code below for one change.
2. If you center text and format unwanted lines to white color, the unmerged range will look identical to the merged one, see post #41. However, as I’m already having success with merged cells on my end, we could keep them.

' standard module
Sub Button1_Click()
Load UserForm1
UserForm1.Show vbModeless
End Sub

 

[NimishK]

Worf
Have used On Local Error Resume next in Excel 2007 before following

ws.HPageBreaks(j - 1).Delete

with implementation of "on line Error....." it Works Perfectly but what happens that it leaves a blank line after Pagebreak
should be manageable.

Should we end here ?
If yes = True Then
Worf Thank you so much for the very very very Hard efforts you have really put in.
Else

Thanks once more

El Ultimo Gol De Pele (Worf)

End IF

NimishK

 

[Worf]

Let’s continue. This should eliminate the blank row:

Sub No_blanks_after_break()
Dim i%, ws As Worksheet, r%
Set ws = ActiveSheet
For i = ActiveSheet.HPageBreaks.Count To 1 Step -1
    r = ws.HPageBreaks(i).Location.Row
    If BlankRange(ws.Rows(r)) And ws.Cells(r + 1, 1) = "Sr. No" Then
        ws.Rows(r + 1).Copy ws.Rows(r)
        ws.Rows(r + 1).Delete
    End If
Next
End Sub


Function BlankRange(p As Range) As Boolean
BlankRange = False
If WorksheetFunction.CountBlank(p) = p.Rows.Count * p.Columns.Count Then BlankRange = True
End Function

 

[NimishK]

Yes Done works Perfectly in Excel 2007
Thank you so much