Solved: Repeating Rows at the BOTTOM

[AdrianK]

Hello,

Excel has a "Rows to repeat at top", but no way to set rows to repeat at the bottom of a page.

From my inital checks I haven't been able to find any way to perform this.

I can't put the information in the footer as it requires formatting (Cell fill etc.), and is for appearances rather than practicality (Trying to fit some rigid marketing guidelines).

Is there a way that this can be performed either through a VBA function, or through conditional formatting maybe?

Many thanks,

Adrian

[mdmackillop]

Footers are a pain!
Simpest way I found was to save a footer on a spare sheet and copy/insert it at the required locations. This depends upon no changes in row heights, otherwise you need to detect page breaks, and this can get tricky. If you can post a sample layout, we can assist further if required.

[AdrianK]

Unfortunately that isn't really an option, as this is line level customer data that, sorted and subtotalled by client, so could be up to 50+ pages and would need to be completed for each client every month (40+ reports)

That would be a lot of manual pasting every month!

Is there any way in excel (aside from manually) finding the page breaks, i.e. can this be done in code?

Many thanks,

Adrian

[mdmackillop]

1. Are all your rows the same height?
2. Will the footer contain subtotals?
3. Do subtotals, if any, carry forward to the next page?

[AdrianK]

Hello.

1) Yes, all the rows are the same height (well, all pages will contain the same number of lines, as the only 2 rows of different height are set to repeat at the top)

2) No, ideally this will be a filled line to match the rows to repeat at top colour scheme. This footer would be below the subtotal row.

3) Yes, some carry over a couple of pages, but i'm not too worried about those. It's really insuring that the formatting can be between the sets of information.

I was just thinking, I have already formatted the subtotal levels, with relevant font/colour/fill, and have set these to page break between groups. Is there any way to set the subtotal level to be several lines deep rather than just one?

If so could this formatting be contained within these, a that would mean they apppeared just before the page break?

Cheers,

Adrian

[AdrianK]

Hello,

I think i've solved this, but adding another level of subtotals (a dummy, count row) which allows me to set the formatting on this row, and ensure it appears just before the page break.

It's not perfect as dependent on the amount of information in each group it doesn't always appear at the bottom of the page, but is is always after the last line of data, so it's an improvement.

I'm writing the subtotals into a macro with a shorcut to allow this to be easily repeated, so that will allow it to be easily repeated.

If anyone knows of a better way, then please do let me know.

Cheers,

Adrian

[mdmackillop]

If you can post a sample workbook, it will be clearer what you are trying to do.

[AdrianK]

I have attached a sample file.

The first tab ("Purchase Graphs - Weight") contains the look I am trying to achieve, this is based on other marketing materials in the format with coloured bars at the top and bottom.

The second tab ("Detail by customer") is an example of the type of page where this is causing issues, as the number of orders by customer will change each month, and there is no fixed limit to how many rows of data this will contain.

All I really want is for the coloured title line (easy at the top with rows to repeat) to appear at the bottom of each page, under the relevant data.

Pre printing the line isn't an option as the colours vary based on the report.

Any ideas?

Cheers,

Adrian