Back to Printing in Excel archive index

Back to archive home

CAN I SET MY PRINT AREA TO THE LAST BLANK LINE ON A SHEET EACH TIME I PRINT WITH MY MACRO?

I presume you mean the last NON-Blank line. If so :-

Sub Set_Print_Area()

Dim x As Long, lastCell As Range

x = ActiveSheet.UsedRange.Columns.Count

Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address

End Sub

That does work fine only problem is i have formulas in my cells...and it picks those cells also

Try this :-

Sub Set_Print_Area()

Dim lastCell As Range

Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)

Do Until Application.Count(lastCell.EntireRow) <> 0

Set lastCell = lastCell.Offset(-1, 0)

Loop

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address

End Sub

ok the the formula woked like it was supposed to but..i have line numbers in column A which i need to exclude from the selection set...also for some reason the code selected out to column AZ...i don't have any thing out there that i know of

: That does work fine only problem is i have formulas in my cells...and it picks those cells also :

Try this :-

Sub Set_Print_Area()

Dim x As Long, lastCell As Range, LR As Long

x = ActiveSheet.UsedRange.Columns.Count

Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)

LR = lastCell.Row

Do Until Application.Count(Range(Cells(LR, 2), Cells(LR, 256))) <> 0

Set lastCell = lastCell.Offset(-1, 0)

LR = lastCell.Row

Loop

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address

End Sub

ok the the formula woked like it was supposed to but..i have line numbers in column A which i need to exclude from the selection set...also for some reason the code selected out to column AZ...i don't have any thing out there that i know of : Try this :- : Sub Set_Print_Area() : Dim lastCell As Range .Offset(1, 0) : Do Until Application.Count(lastCell.EntireRow) <> 0 : Set lastCell = lastCell.Offset(-1, 0) : Loop :

That works better. But it still is using column A in the blank row criteria

P.s. sorry about the goal posts..but you almost had your field goal anyway

, LR As Long .Offset(1, 0) LR = lastCell.Row Do Until Application.Count(Range(Cells(LR, 2), Cells(LR, 256))) <> 0 LR = lastCell.Row

Well it works for me and excludes rows at the end of the sheet that contain no numbers in columns B:IV. You must have a number in a column after what you think is your last column.

Select all columns after your last column, delete them, save the workbook, and then see if it works.

Do the same for rows below your last row.

Select all columns after your last column, delete them, save the workbook, and then see if it works.

Do the same for rows below your last row.

Do you have a Total row or something after the "blank" rows that you want excluded?

What formulas do you have in your "blank" rows?

Are they displaying a result other than "" ?

You are not treating a cell which shows a 0 as a blank cell, are you?

Also, try this :-

Insert a column before Column A.

Put this formula in A1 and fill down :

=COUNT(C1:IV1)

For the rows that should be excluded, the formula result should be 0. If it isn't, the row cannot be displaying all blank cells.

That works better. But it still is using column A in the blank row criteria P.s. sorry about the goal posts..but you almost had your field goal anyway : Try this :- : Sub Set_Print_Area() , LR As Long .Offset(1, 0) : LR = lastCell.Row : Do Until Application.Count(Range(Cells(LR, 2), Cells(LR, 256))) <> 0 : Set lastCell = lastCell.Offset(-1, 0) : LR = lastCell.Row : Loop

I am comming up with a 12 when i use the count function (which is right, I have 12 fields of info)

the formula in the body of the print area is

=Sheet3!AT2, =Sheet3!AU2, etc.

=Sheet3!AT3, =Sheet3!AU3, etc.

Those numbers I speak of in column A are always there but the when results of the formula in B = 0 is what I need to set as my last line. Well it works for me and excludes rows at the end of the sheet that contain no numbers in columns B:IV. You must have a number in a column after what you think is your last column. Select all columns after your last column, delete them, save the workbook, and then see if it works. Do the same for rows below your last row. Do you have a Total row or something after the "blank" rows that you want excluded? What formulas do you have in your "blank" rows? Are they displaying a result other than "" ? You are not treating a cell which shows a 0 as a blank cell, are you? Also, try this :- Insert a column before Column A. Put this formula in A1 and fill down : =COUNT(C1:IV1) For the rows that should be excluded, the formula result should be 0. If it isn't, the row cannot be displaying all blank cells. : That works better. But it still is using column A in the blank row criteria : P.s. sorry about the goal posts..but you almost had your field goal anyway :

the formula in the body of the print area is

=Sheet3!AT2, =Sheet3!AU2, etc.

=Sheet3!AT3, =Sheet3!AU3, etc.

Those numbers I speak of in column A are always there but the when results of the formula in B = 0 is what I need to set as my last line. Well it works for me and excludes rows at the end of the sheet that contain no numbers in columns B:IV. You must have a number in a column after what you think is your last column. Select all columns after your last column, delete them, save the workbook, and then see if it works. Do the same for rows below your last row. Do you have a Total row or something after the "blank" rows that you want excluded? What formulas do you have in your "blank" rows? Are they displaying a result other than "" ? You are not treating a cell which shows a 0 as a blank cell, are you? Also, try this :- Insert a column before Column A. Put this formula in A1 and fill down : =COUNT(C1:IV1) For the rows that should be excluded, the formula result should be 0. If it isn't, the row cannot be displaying all blank cells. : That works better. But it still is using column A in the blank row criteria : P.s. sorry about the goal posts..but you almost had your field goal anyway :

if i delete the formulas from the cells...the set print area works properly...but i need the formulas in each of those cells.. unless i can copy and paste the results from my sort to this page insted of having it equal my result page.

So what you are saying is that the cells you want excluded are not in fact blank but display a zero.

Also, the last row of data is not, in fact, the last row on the sheet since there is a Total row beneath the data.

It is a bit difficult to come up with a macro when the information about the sheet set-up is supplied in bits and pieces a little at a time, and changes each time.

I now have a whole bunch of new questions that need answering before I can do a revised macro.

I suggest that you start a new thread and ask that someone look at your spreadsheet so that a macro can be done that fits the sheet conditions.

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.