Setting print area using VBA

Using VBA, how do I select my Print Area to be the first cell in use (normally A1) to the last cell with data in it ?
In addition I would like to be able to set up how many pages I want the print the result… for example 1 page wide by two pages long.

Thanks
GordonMassonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rory ArchibaldConnect With a Mentor Commented:
The function goes into the same module as your current code.

I made a mistake - the printarea line should be:


   .PrintArea = ActiveSheet.Range(ActiveSheet.UsedRange.Cells(1), LastCellInRange(ActiveSheet.UsedRange)).Address
0
 
RushBCommented:
Try this:

lastrow = Cells.SpecialCells(xlLastCell).Row
This selects the last used row in the worksheet

lastrow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
This select the last used cell in Column G

This macro sets the print area, you must choose which 'lastrow' you
want and which column.

Sub stance()
lastrow = Cells.SpecialCells(xlLastCell).Row
lastrow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = "$A$1:$G$" & lastrow
End Sub



Thanks,
RushB
0
 
Rory ArchibaldCommented:
Something like:

With Activesheet.PageSetup
   .Zoom = False
   .FitToPagesWide = 1
   .FitToPagesTall = 2
   .Printarea = Activesheet.usedrange.address
End With

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
GordonMassonAuthor Commented:
Roya

That works very well but i have some cells which have a formula in them but the cell text is blank
Is there a way to make it work with the last cell with data rather than the last cell with a formula?

Thanks
0
 
Rory ArchibaldCommented:
Add the function below, then use:

With Activesheet.PageSetup
   .Zoom = False
   .FitToPagesWide = 1
   .FitToPagesTall = 2
   .Printarea = Activesheet.range(activesheet.usedrange(1), lastcellinrange(activesheet.usedrange)).address
End With
Public Function LastCellInRange(rngInput As Range) As Range
   Set LastCellInRange = rngInput.Cells(1)
   On Error Resume Next
   With rngInput
      Set LastCellInRange = .Cells.Find(What:="*", After:=.Cells(1), lookat:=xlWhole, LookIn:=xlValues, _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious)
   End With
End Function

Open in new window

0
 
GordonMassonAuthor Commented:
Hi Roya

Thanks for your help.
i am not sure where the Function should go?
Is it in a module or in teh Workbook.......

When i try to run the sub i get a fault on the .printarea line:
Wrong number of arguments or invalid property assignment.
Any ideas what i am doing wrong?

Thanks
0
 
GordonMassonAuthor Commented:
Hi Roya

Thats got rid of the error but my print area is still wrong.

I do have borders round some of the empty cells. Is this the problem?
0
 
GordonMassonAuthor Commented:
Sorry Roya.... think i have that wrong... just checking what i have done
0
 
GordonMassonAuthor Commented:
Sorry Roya i got that wrong.
The problem was that i had two sheets open and the code was running on the wrong active sheet.
So.... to make sure i dont have this problem again is ther a mod you can do that ensures that the code runs in the current workbook?

Thanks
0
 
GordonMassonAuthor Commented:
Thanks Roya

SOrted now, used ThisWorkbook.Activate to get me onto the right page.

Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.