Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding in row at bottom of range
I have a named dynamic range for printing, ie AATableA which is defined
as =OFFSET('Table A Master'!$A$1,0,0,'TableA Input'!$B$160,18) The part of the procedure below prints out only those rows which have continuous entries and leave all the blank rows below. This works fine but on row 160 there are totals for the columns already in the printout. What I want to do ideally is to print out the completed rows as I do already but somehow in the procedure add in the totals row at the bottom of the printed rows. Is there some way to add this to the print area or does it have to be re-written in another procedure. ActiveSheet.PageSetup.PrintArea = "AATableA" With ActiveSheet.PageSetup .PrintTitleRows = "$1:$5" .Orientation = xlLandscape .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .LeftFooter = "&F" End With ActiveWindow.SelectedSheets.PrintOut Grateful as always for any guidance. Graham Haughs Turriff Scotland |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding in row at bottom of range
What I got out of that is you want to expand the printarea by 1 row?
Dim rng as Range set rng = Range("AATableA") set rng = rng.resize(rng.rows.count+) ActiveSheet.PageSetup.PrintArea = rng.Address(external:=True) With ActiveSheet.PageSetup .PrintTitleRows = "$1:$5" .Orientation = xlLandscape .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .LeftFooter = "&F" End With ActiveWindow.SelectedSheets.PrintOut -- Regards, Tom Ogilvy "Graham Haughs" wrote in message ... I have a named dynamic range for printing, ie AATableA which is defined as =OFFSET('Table A Master'!$A$1,0,0,'TableA Input'!$B$160,18) The part of the procedure below prints out only those rows which have continuous entries and leave all the blank rows below. This works fine but on row 160 there are totals for the columns already in the printout. What I want to do ideally is to print out the completed rows as I do already but somehow in the procedure add in the totals row at the bottom of the printed rows. Is there some way to add this to the print area or does it have to be re-written in another procedure. ActiveSheet.PageSetup.PrintArea = "AATableA" With ActiveSheet.PageSetup .PrintTitleRows = "$1:$5" .Orientation = xlLandscape .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .LeftFooter = "&F" End With ActiveWindow.SelectedSheets.PrintOut Grateful as always for any guidance. Graham Haughs Turriff Scotland |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding in row at bottom of range
Sorry Tom, I made a mess of trying to explain that. Yes I do want to
expand the range by one row but it is to to be a specific row, ie row 160 where the totals are. The range has 159 rows but perhaps only 30 are filled at any time and the totals of the numbers in the columns in these rows is shown in row 160. The "AATableA" just prints out these 30 rows with the column titles and ignores the blank rows but I would like the print to contain row 160 as well showing the totals.ie I would like the printout in this case to show rows 1 to 30 with row 160 as row 31 so to speak. I hope that is a bit clearer with my apologies for confusion and thanks for your patience. Graham Haughs Tom Ogilvy wrote: What I got out of that is you want to expand the printarea by 1 row? Dim rng as Range set rng = Range("AATableA") set rng = rng.resize(rng.rows.count+) ActiveSheet.PageSetup.PrintArea = rng.Address(external:=True) With ActiveSheet.PageSetup .PrintTitleRows = "$1:$5" .Orientation = xlLandscape .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .LeftFooter = "&F" End With ActiveWindow.SelectedSheets.PrintOut |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding in row at bottom of range
You would need to hide the rows in-between
Dim rng as Range Dim rng2 as Range set rng = Range("AATableA").columns(1) set rng2 = Range(rng(rng.count+1),cells(159,18)) rng2.entirerow.Hidden = True ActiveSheet.PageSetup.PrintArea = _ Range(rng(1),Cells(160,18)).Address(external:=True ) With ActiveSheet.PageSetup .PrintTitleRows = "$1:$5" .Orientation = xlLandscape .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .LeftFooter = "&F" End With ActiveSheet.PrintOut rng2.EntireRow.Hidden = False -- Regards, Tom Ogilvy "Graham Haughs" wrote in message ... Sorry Tom, I made a mess of trying to explain that. Yes I do want to expand the range by one row but it is to to be a specific row, ie row 160 where the totals are. The range has 159 rows but perhaps only 30 are filled at any time and the totals of the numbers in the columns in these rows is shown in row 160. The "AATableA" just prints out these 30 rows with the column titles and ignores the blank rows but I would like the print to contain row 160 as well showing the totals.ie I would like the printout in this case to show rows 1 to 30 with row 160 as row 31 so to speak. I hope that is a bit clearer with my apologies for confusion and thanks for your patience. Graham Haughs Tom Ogilvy wrote: What I got out of that is you want to expand the printarea by 1 row? Dim rng as Range set rng = Range("AATableA") set rng = rng.resize(rng.rows.count+) ActiveSheet.PageSetup.PrintArea = rng.Address(external:=True) With ActiveSheet.PageSetup .PrintTitleRows = "$1:$5" .Orientation = xlLandscape .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .LeftFooter = "&F" End With ActiveWindow.SelectedSheets.PrintOut |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding in row at bottom of range
Tom
Sorry for delay in responding but have been away and only in last hour have seen response. This works perfectly so again I am indebted to you for your time and effort. Many thanks. Graham Tom Ogilvy wrote: You would need to hide the rows in-between Dim rng as Range Dim rng2 as Range set rng = Range("AATableA").columns(1) set rng2 = Range(rng(rng.count+1),cells(159,18)) rng2.entirerow.Hidden = True ActiveSheet.PageSetup.PrintArea = _ Range(rng(1),Cells(160,18)).Address(external:=True ) With ActiveSheet.PageSetup .PrintTitleRows = "$1:$5" .Orientation = xlLandscape .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .LeftFooter = "&F" End With ActiveSheet.PrintOut rng2.EntireRow.Hidden = False |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
starting from bottom of range instead of top | Excel Programming | |||
how use range to bottom of filled cells? | Excel Worksheet Functions | |||
Adding info in the row at the bottom using macro? | Excel Discussion (Misc queries) | |||
Copying formula to the bottom of a range | Excel Programming | |||
How to: Add blank row at bottom of range | Excel Programming |