Print area. in macro/VBA

[Lallo]

I have a worksheet with billing information.

Column A (A6 and down) marks whether an invoice has been entered (= 1) or not (=-1). There are also codes "-2" and "0" (zero)

From the beginning there are 2000 rows of "-1" in this column. This is safely over the maximum number of invoices in a single project.

The problem is, I want to print Columns A through P, but only Rows 1 through to the last sent invoice, is the last row that does not contain "-1" in column A. If not, it will print page upon page of -1's with no information in the rest of the rows.



What I (think I) need to do is to

1) Find the last cell with not "-1" in column A. Name this "lastCell"
2) Mark Columns A through P, And rows 1 through lastCell
3) Print selection

Any suggestions?

 

[Blunder1]

Try (you can adjust teh page set up to suit):-

LastRow = Cells.Find("-1").Row
LastRow = LastRow - 1
ActiveSheet.PageSetup.PrintArea = Range("A1:P" & _
Range("a" & LastRow).Row).Resize.Address
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FitToPagesWide = 1
.FitToPagesTall = 4

End With

 

[kurchev]

You can put a filter on Column A that would hide any row with "-1". I typically test for rows I want to print and place an "X" in a hidden column to filter on. Then you can place a Print button on the page, and code it to first apply the filter for range A6:P2000, then print the document.

I'm not able to get to an example code insert from my files right now, but I can grab one for you later.

-AK

 

[Lallo]

Thank you both for your time and effort!

I solved it another way in the end, going by the last row of text in the "information" i.e by searching for the last line with text in the rows for "company name" "contact" and so forth...

Here's what I used (with great help of various message boards)

Sub MarkAndPrint()

'Sets Print Area and prints
Dim LR As Long
LR = Columns("C:K").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

With ActiveSheet
.PageSetup.PrintArea = "A1:Q" & LR
.PageSetup.Orientation = xlLandscape
.PageSetup.FitToPagesWide = 1
.PrintOut

End With

End Sub

Perhaps not the prettiest code, but it does the job...

Again, thanks for your help!