Solved

Setting print area using VBA

Posted on 2010-11-25
10
517 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:GordonMasson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 3

Expert Comment

by:RushB
ID: 34212986
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34213011
Something like:

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

Open in new window

0
 

Author Comment

by:GordonMasson
ID: 34213055
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34213091
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
 

Author Comment

by:GordonMasson
ID: 34213119
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34213126
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
 

Author Comment

by:GordonMasson
ID: 34213156
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
 

Author Comment

by:GordonMasson
ID: 34213182
Sorry Roya.... think i have that wrong... just checking what i have done
0
 

Author Comment

by:GordonMasson
ID: 34213224
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
 

Author Closing Comment

by:GordonMasson
ID: 34215985
Thanks Roya

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

Thanks for your help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question