Print footer directly after last row in page

[YasserKhalil]

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 ..

[HansV]

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.

[YasserKhalil]

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?

[HansV]

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.

[YasserKhalil]

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

[HansV]

As I mentioned, margins apply to ALL pages, not just the last page.

[YasserKhalil]

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

[YasserKhalil]

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

[HansV]

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.

[YasserKhalil]

Thanks a lot for reply Mr. Hans
No problem of your suggestion and I hope I can suit it in the original file

[YasserKhalil]

Can you please help me with your suggestion?

[HansV]

You know how to find the end of the used range.
Place the text you need below it...

[YasserKhalil]

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

[YasserKhalil]

Is there any chance for helping me in this topic please?

[Guessed]

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.

[YasserKhalil]

Hello. I am sorry I didn't get what you mean

[YasserKhalil]

I have posted here too
https://www.excelforum.com/excel-progra ... ost5044865" onclick="window.open(this.href);return false;

[YasserKhalil]

This is my try till now

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

Hope to find a way to deal with just the last page?