Macro to define a print area

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know of a macro that when ran would check row by row and when a row was found that was blank it would set the print area for the row above it and on up. I have useres that may have 300 rows or may just have 10 rows. I would like to be able to have them click on a button, (macro), and the macro would search for the first blank row and set the print margin to that.
I need this because right now if the person with 300 rows goes to print its fine but if the person with 10 rows prints it ends up printing 150 blank pages.

Thank you
 
Hi JRoberts

If you clear you printarea Excel will only print the data that is on the sheet.
File>Print Area....Clear Print Area

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




JRoberts said:
Does anyone know of a macro that when ran would check row by row and when a row was found that was blank it would set the
Click to expand...
print area for the row above it and on up. I have useres that may have 300 rows or may just have 10 rows. I would like to be
able to have them click on a button, (macro), and the macro would search for the first blank row and set the print margin to
that.
 
You have two possible ways to find "the first blank row" and which you use
depends on the layout of your data. If you have data in rows 1-100 and row
37 is blank, then the first blank row from the top is row 37. If the macro
looks down from row 1 it will find row 36 and set the print range rows 1-36.
If this is what you want printed in this example, then the first of the two
statements below is what you want.
However, if you want the macro to set the print area to rows 1-100
regardless of any blank rows in between, then you want to use the second of
the two statements below. Use the one that best fits
your data.

The following statement will set the print range as A1:A36 in the above
example.
SetPrintRng=Range("A1", Range("A1").End(xlDown))

The following statement will set the print range as A1:A100 in the above
example.
SetPrintRng=Range("A1", Range("A" & Rows.Count).End(xlUp))

Please post back if you need more than this. HTH Otto

JRoberts said:
Does anyone know of a macro that when ran would check row by row and when
Click to expand...
a row was found that was blank it would set the print area for the row above
it and on up. I have useres that may have 300 rows or may just have 10 rows.
I would like to be able to have them click on a button, (macro), and the
macro would search for the first blank row and set the print margin to that.
I need this because right now if the person with 300 rows goes to print
Click to expand...
its fine but if the person with 10 rows prints it ends up printing 150 blank
pages.
 
That didnt solve the problem I was trying to fix. The problem I have is that I have an excel sheet which has two columns with formulas in it. These two columns go down about 3000 rows. I have hidden these two columns so that the end user does not see them and thought that this would work. In Office 2002 If I go to print it will only print what the user types in the other cells and does not print the hidden columns. This you get 2-4 pages which is good. But if the user is using Office 2000 or earlier and go print they print 300 blank pages because its picking up on the hidden columns. I think its doing this because the two hidden cells add two other cells to get a total. So by default the cell would be 0 and that is why I think it is printing. I know this is confusing so if you need you can email me at (e-mail address removed) and I will email you a copy of the excel sheet so you can see what I am talking about.
 
Could it be that you have manual page breaks in the range of hidden rows?

But this may help either way:

Option Explicit
Sub testme01()

Dim LastRow As Long
Dim LastCol As Long
With ActiveSheet
LastCol = 10 'column J is 10
LastRow = .Evaluate("Max(Row(a1:a9999)*(a1:a9999<>""""))")

.PageSetup.PrintArea = .Range("A1").Resize(LastRow, LastCol).Address

.PrintOut preview:=True
End With

End Sub

I guessed at the lastcol being column J (=10). And I guessed that column A
should be the column that determines the last row. Change as required. (and
drop the preview:=true when you're happy (I added it to save a few trees).)

(and extend that 9999 stuff as far as you need, but don't use the whole column
(65536 will cause an error.)
 
Dave:

How do you modify the code to also do a look up on the column? Tried:

Sub testme01()

Dim LastRow As Long
Dim LastCol As Long
With ActiveSheet
LastCol = .Evaluate("Max(Col(b4:k4)*(b4:k4<>""""))")
LastRow = .Evaluate("Max(Row(b2:b150)*(b2:b150<>""""))") - 1


but get an error on the LastCol command
 
Try:

LastCol = .Evaluate("Max(Column(b4:k4)*(b4:k4<>""""))")

Notice the spelling of Column <bg>.
 
Back
Top