Forum Discussion

DHR1982's avatar
DHR1982
Copper Contributor
Jan 13, 2021

Conditional page break position

Hi

 

Is it possible to set page breaks in Excel to automatically adjust to a specific row/column depending on which rows/columns are visible (so not hidden) at the time? I cannot seem to make this work. 

 

I have a spreadsheet with over 300 rows of data, and VBA code that hides none relevant rows each time data is entered. However I then need to manually adjust the page breaks if I need to save as a PDF or print, and I'm just trying to automate that process if possible. 

 

I'd rather not write more VBA code if I don;t have to, so just reaching out to the community for any expertise / tips please. 

 

Thanks

excel
Formulas and Functions
Macros and VBA
  • DLSMIC's avatar
    DLSMIC
    Copper Contributor
    Jul 29, 2022

    DHR1982, I am struggling with a similar same problem. I have tables with about 1000 lines and I want to introduce a page break between groups with up to 40 lines each. Everything should be dynamic. I would love to see page breaks in conditional formatting. For now I can share my work around:

    Lets say Table1 is the Table that I want to split over multiple pages. In a new sheet I insert the following formula:

    =LET(pagesize;49;cols2copy;3;
groups;UNIQUE(Table1[Group]);
numberofgroups;ROWS(groups);
rowsingroup;MAP(groups;LAMBDA(x;COUNTIF(Table1[Group];x)));
groupoffset;XMATCH(groups;Table1[Group];0);
groupstart;SCAN(1;groupoffset;LAMBDA(a;x;CEILING(a+x-2;pagesize)+1));
groupend;MAP(SEQUENCE(numberofgroups);LAMBDA(x;INDEX(groupstart;x)+INDEX(rowsingroup;x)-1));
totalrows;INDEX(groupstart;numberofgroups;1)+INDEX(rowsingroup;numberofgroups;1)-1;
MAKEARRAY(totalrows;cols2copy;LAMBDA(r;c;LET(group;XMATCH(r;groupstart;-1);rowintable;r-INDEX(groupstart;group)+INDEX(groupoffset;group);IF(r<=INDEX(groupend;group);INDEX(Table1[#Data];rowintable;c);"")))))

     The idea is that each page will have the same number of rows (49). So it does not work if there are cells with line breaks.

     

    Is there a better way to do this?

Resources