Set some Excel records as a fixed footer on the end of a page

[johan]

Newsgroupers

I have a worksheet with 50 records. The last 5 records are on the
final end of the page and were filled in as a footer for this page.
Now I had hidden a few records divided over the records 1 till 45
because I didn't want to print the text that were registrated on it.
When printing this page the 'footer-records' (record 46 till 50) stay
somewhere in the middle (locical because I had hidden some records and
everything then will roll up).

My question is that I want my records 46 till 50 always on the end of
my page.
I want to set them as a kind of fixed footer independend of the number
of hidden rows.

Somebody know how to do this ?

regards,
Johan

 

[Bill Kuunders]

this macro should do the trick.
it will count how many hidden rows you have got at the time
insert rows just above row 45
do a print and delete the inserted rows

you may need to adjust ranges depending on your layout

Sub footer()
Dim hiddencount As Integer

Sheet1.Select
hiddencount = 0
For i = 1 To 45
If Rows(i).Hidden Then
hiddencount = hiddencount + 1

End If
Next i
Range("a45", Range("a45").Offset(hiddencount - 1, 0)).Select
Selection.EntireRow.Insert
ActiveWindow.SelectedSheets.PrintOut copies:=1
Selection.EntireRow.Delete

Range("a1").Select
End Sub