Print footer directly after last row in page
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Print footer directly after last row in page
Hello everyone
I have a sheet that I made a code to print the sheet and the data in the sheet is dynamic by another code. I mean the number of rows that will be printed will not be fixed all the time
Is there a way to print the footer directly after the last row of each page as the page sometimes has only 5 rows and at this case the footer is far away from the data .. so I need a way to make the footer near the data in that case? Is that possible?
Note I don't want to include the footer as temporary row and delete that row after the print ..
I have a sheet that I made a code to print the sheet and the data in the sheet is dynamic by another code. I mean the number of rows that will be printed will not be fixed all the time
Is there a way to print the footer directly after the last row of each page as the page sometimes has only 5 rows and at this case the footer is far away from the data .. so I need a way to make the footer near the data in that case? Is that possible?
Note I don't want to include the footer as temporary row and delete that row after the print ..
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Print footer directly after last row in page
The page footer is by definition at the foot of the page, not directly below the last used row.
The option that you don't want to use would be the only workaround, so you have made it impossible to solve your problem.
The option that you don't want to use would be the only workaround, so you have made it impossible to solve your problem.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Print footer directly after last row in page
I thought another idea ..which is to change the margins of the footer but I have no idea how to apply it. Is that possible in that case?
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Print footer directly after last row in page
They would apply to all pages; if you only have one page it might work. But placing the "footer" in the worksheet itself would be much easier.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Print footer directly after last row in page
Thanks a lot Mr. Hans
I have multiple pages so if there is a way to make the margin for each page is fixed except for the last page. I think this will be suitable
I have multiple pages so if there is a way to make the margin for each page is fixed except for the last page. I think this will be suitable
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Print footer directly after last row in page
As I mentioned, margins apply to ALL pages, not just the last page.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Print footer directly after last row in page
I mean to change the margin by code so as to be usual for the first pages then as for the last page to change to suit then at the end of the code to return to the usual margin again
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Print footer directly after last row in page
How to loop through the pages in the sheet or how to determine the number of pages ...?
If there is a way we can loop through each page and before the loop put the margin to be equal to the usual margin and in the loop we can put a IF statement as for the last page and in the last page we change the margin to be suitable to that last page .. But I don't know if it is possible by code to know how to suit this
I am just thinking loudly
If there is a way we can loop through each page and before the loop put the margin to be equal to the usual margin and in the loop we can put a IF statement as for the last page and in the last page we change the margin to be suitable to that last page .. But I don't know if it is possible by code to know how to suit this
I am just thinking loudly
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Print footer directly after last row in page
I suggest placing the text in the worksheet itself. If you don't want that, I'll leave it to you to find a solution.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Print footer directly after last row in page
Thanks a lot for reply Mr. Hans
No problem of your suggestion and I hope I can suit it in the original file
No problem of your suggestion and I hope I can suit it in the original file
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Print footer directly after last row in page
Can you please help me with your suggestion?
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Print footer directly after last row in page
You know how to find the end of the used range.
Place the text you need below it...
Place the text you need below it...
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Print footer directly after last row in page
Yes that would be easy for one page but the desired result is to put the footer to each page .. and the number of pages are not fixed
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Print footer directly after last row in page
Is there any chance for helping me in this topic please?
-
- 2StarLounger
- Posts: 102
- Joined: 04 Feb 2010, 22:44
- Location: Melbourne Australia
Re: Print footer directly after last row in page
Yasser
Centre align the content on the page. That way the spacing to the footer will only be be half as big as it currently is.
Centre align the content on the page. That way the spacing to the footer will only be be half as big as it currently is.
Andrew Lockton
Melbourne Australia
Melbourne Australia
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Print footer directly after last row in page
Hello. I am sorry I didn't get what you mean
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Print footer directly after last row in page
I have posted here too
https://www.excelforum.com/excel-progra ... ost5044865" onclick="window.open(this.href);return false;
https://www.excelforum.com/excel-progra ... ost5044865" onclick="window.open(this.href);return false;
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Print footer directly after last row in page
This is my try till now
Hope to find a way to deal with just the last page?
Code: Select all
Sub Test()
Dim i As Integer, x As Double, lr As Integer
Dim a, y
With ActiveSheet.PageSetup
x = .FooterMargin
For i = 1 To .Pages.Count
If i = .Pages.Count Then
'Stop
'How to set the margin so as to suit the last row of data
lr = Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = Range("A1:H" & lr).Address
a = Mid(Sheets("Sheet1").HPageBreaks(1).Location.Address(0, 0), 2, 99) - 1
y = a - (lr - (a * (.Pages.Count - 1)))
MsgBox y
'.FooterMargin = Application.InchesToPoints(0.78740157480315)
End If
ActiveSheet.PrintOut from:=i, To:=i, Preview:=True
'ActiveSheet.PrintPreview
Next i
.FooterMargin = x
End With
End Sub