Indicate the row number for each last row in each new page printed

**clng** · 11-17-2008, 01:39 AM

Dear all,

I have this question for indentifying the last row (number) for each and every page to be printed.

As I am using a loop to determine the last row of the table in each print page, I am only able to do in "hard coded". And with this "hard-coded" row number, I can set bordering to the table so that table will not be printed without any border (for in between pages if table is more than a page)

But the problem is that I had discovered that once any other user start using wrap text, or even change the row height, the macro will still captures the pre-determined no. of rows to do the bordering (as shown in my code).

In my, I had restricted the zoom size so that the quotation will always be the same format. Hence, based on this control zoom, the printable page changes if the row height changes too......

With destSh.PageSetup
.PrintArea = "$A1:$H" & LastR
.PrintTitleRows = "$1:$21"
.Zoom = 62
' .FitToPagesWide = 1
' .FitToPagesTall = 8
.PrintErrors = xlPrintErrorsDisplayed
.RightFooter = "&8Last Saved : " & _
Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _
"yyyy-mmm-dd hh:mm:ss")
.CenterFooter = "Page &P of &N"
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
End With
'for automatic border underline
ub = 77
For fndline = ub To lastprintable
If lastprintable > ub Then
destSh.Range(Cells(ub, 1), Cells(ub, 8)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

ub = ub + 56
End If
Next fndline

pages = ExecuteExcel4Macro("Get.Document(50)")
destSh.Range("D12").Value = "Pages (Incl this page) : "
destSh.Range("G12").Value = pages
destSh.Range("G12").Select
Selection.NumberFormat = "General"

Can anyone show me how to change the "hard-coded" row number

ub = ub + 56

to a dynamic row count that let me know that for example: in page one, there are 20 rows in the table, in page 2, there are 30 rows in the table and if page 3 is the last page, I will use a find function to locate the last row number of the table, and if the table ends at page 3, nothing will be done as the table is already having border.

Thanks in advance.

Cheers,
CL

**clng** · 11-20-2008, 05:41 AM

Can anyone help with my problem?? Thanks in advance!!

CL

**royUK** · 11-20-2008, 06:05 AM

Attach a small example workbook

**clng** · 11-20-2008, 10:26 AM

Hi royUk,

Nice to hear from you again. I don't have the file with me now. I will post it tomorrow. Hope to hear from you again.

Cheers,
CL

**clng** · 11-20-2008, 10:09 PM

Hi royUK,

I am attaching a scale down version with just the issues that I am asking.

As in the attachment, I had pre-determined the number of rows for the quotation table for each and every print page.

Again, I would like to state that my problem is that whenever I expand any row height within the table, there is a high chance that the number of rows of table in each print page will also change.

But using my method to pre-determine the number of rows for the quotation table, border setup will be in-between the table and not at the last row of the table for each print page.

I am also getting VBA to generate a print preview immediately after hitting the "Convert to Quotation Form" button, so you will see that all formating and resizing has been fixed to standardize the quotation print output.

Please help me and advice me on how to get VBA to recognize the last row count of the table for each print page to be dynamic and following the way this function identify pages

ExecuteExcel4Macro("Get.Document(50)")

.

Thank you once again.

Cheers,
CL

**clng** · 11-23-2008, 10:19 AM

Hi royUK,

Hope that I did not miss you on last latest posting (with workbook example). Hope to hear from you and I really need some help on this trouble-shooting of code.

Cheers,
CL

**royUK** · 11-24-2008, 12:51 PM

maybe you could use the HPageBreaks collection

Please Login or Register  to view this content.

**clng** · 11-24-2008, 11:12 PM

Hi royUK,

The method is ideal for identifying the last row for each page break.

But I also need to capture the value of the row (e.g. row 59 and 59 is the value) instead of just showing message box.

I have tried working along this line but table border macro does not work anymore...

Please help to see my code in the attachment and kindly advice where did I go wrong.

In my code, I am getting the macro to first copy the costing sheet to a new sheet named "Quotation", then I get the macro to do page setup (so that all quotation are standardized on print).
Next, I use the page break macro to determine the number of pages, and within each page, find whether the row value identified is smaller than the last row of the table. If yes, the macro will create a line at the last row of the table on that print page. And the loop goes on till the last row of the last page counted is bigger than the last row value of the table.

Thanks again in advance.

Cheers,
CL

**clng** · 11-25-2008, 04:55 AM

Hi royUk,

I have also discovered that whenever I use the page setup macro, the "line" border creation macro will not work and the pagebreaks count always points to zero. If I remove the page setup, then the pagebreaks will work.

However, if using my macro to create new worksheet, the pagebreaks fails to work again.

Any idea where I had gone wrong?

Thanks again.

Cheers,
CL

**clng** · 11-26-2008, 05:05 AM

Hi roy,

I think I had worked out the Hpagebreak collection sequence. By adding printpreview before the collection macro, it will get the page count to work.

However, I still have a small problem. I am getting out of range subscript error 9.

The problem that I identified is that for the 1st page count, the location row is correct, but when I loop to page 2, the location row still stays unchanged (thus prompting the error).

Can you please kindly advice me on how to work around this (had use error handling but looping thru all page counted, the location row is still the same).

Thanks,
CL

**royUK** · 11-26-2008, 05:16 AM

There doesn't appear to be any page breaks to count. Try running the page break code after the main code, possibly in a separate procedure called at the end of the main code

**clng** · 11-26-2008, 10:00 AM

Hi roy,

Thanks for the tip. But sorry, I am too daft. I had try putting it as a separate procedure and fire it only at the last of the main code (by calling the sub).

However, there is still nothing to count. However, if I am using the current print preview method as an activation first (then clicking on "next" manually till the last of page and close the print preview), then the page break will be functioning properly.

Please help by amending directly onto my attached file (ver 2) so that I can learn where did I go wrong.

Sorry for the request but pagebreak is still too profound for me as a novice.

Thanks again.

Cheers,
CL

**clng** · 12-01-2008, 02:07 AM

Hi all,

I think I had solved the problem but I am aborting this requirement. Reasons being that the macro works only for the default printer. If after the macro had been activated and you decide to print using other network printers or PDF distiller etc., chances that the page preview will be different from your default printer (due to different manufacturers and their software drivers).

The trick is to make the page "move manually" down to the end of pages, then the macro of the pagebreak will start acknowledging and count the pages. When the pages are counted, then the macro of identifying the "last row" of each printed page (pagebreak) can be resolved.

But I am attaching the complete solution (based only on default printer) that shows how you can get a macro to create borderlines for print-outs of a table format that stretches beyond one page of print (usually, you will need to find out where is the page break and then manually do the borderline accordingly before printing).

Cheers,
CL

**royUK** · 12-01-2008, 04:58 AM

Thanks for sharing your solution

**clng** · 12-03-2008, 10:41 AM

Hi roy,

Thanks for all the help and encouragement. Actually, it is also possible to make all print-out adjust to any printers listed in the network... but it is simply too "delibrate" as we need the user to print only using macro by setting default printer as std printer, then change to any one of the printer in the network list (by using application.printer etc.), do page setup again then print out. Way too much to pamper the user.

Anyway, hope I can also start contributing soon.

Cheers,
CL

Indicate the row number for each last row in each new page printed

LinkBack

Thread Tools

Rate This Thread

Display

Indicate the row number for each last row in each new page printed

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions