SETTING A PRINT AREA AUTOMATICALLY

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

it worked but...

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 ........

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 ........

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 ........

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 ........

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)

I think the problem is the formulas

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 .......

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.