Archive of Mr Excel Message Board


Back to Printing in Excel archive index
Back to archive home



SETTING A PRINT AREA AUTOMATICALLY

Posted by RANDY on September 13, 2001 7:54 AM
CAN I SET MY PRINT AREA TO THE LAST BLANK LINE ON A SHEET EACH TIME I PRINT WITH MY MACRO?

Re: SETTING A PRINT AREA AUTOMATICALLY

Posted by Henry Root on September 14, 2001 5:43 AM


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


Re: SETTING A PRINT AREA AUTOMATICALLY

Posted by randy on September 14, 2001 6:26 AM
That does work fine only problem is i have formulas in my cells...and it picks those cells also


Re: SETTING A PRINT AREA AUTOMATICALLY

Posted by Henry Root on September 14, 2001 8:47 AM
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


it worked but...

Posted by randy on September 14, 2001 11:19 AM
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 :


You keep moving the goal posts!! Try this ........

Posted by Henry Root on September 14, 2001 6:47 PM
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 :


Re: You keep moving the goal posts!! Try this ........

Posted by randy on September 18, 2001 5:42 AM
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


Re: You keep moving the goal posts!! Try this ........

Posted by Henry Root on September 18, 2001 6:27 AM
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 : 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


Re: You keep moving the goal posts!! Try this ........

Posted by randy on September 18, 2001 7:14 AM
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 :


shoot..i do have a total column M =sheet3!be3*$c$8(NT)

Posted by randy on September 18, 2001 7:18 AM

I think the problem is the formulas

Posted by randy on September 18, 2001 11:41 AM
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.


Suggestion .......

Posted by Henry Root on September 18, 2001 4:42 PM

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.