Adding in row at bottom of range

[Graham Haughs]

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

 

[Tom Ogilvy]

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

 

[Graham Haughs]

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]

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

 

[Graham Haughs]

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