Free Microsoft Excel 2013 Quick Reference

Excel VBA - Setting Print Area in VB

I have a problem with being able to set the print area using VB.

The Project I am working on has a sheet (Sheet 1) where details are entered, and a second sheet (Sheet 2) picks out certain details using VLOOKUP from Sheet 1. Sheet 1 (and therefore Sheet 2), can have from a minimum of 3 rows anywhere up to 400 rows. I need to be able to print an amount of rows inbetween 3 and 400 from Sheet 2.

On Sheet 2 , there are columns which have details in them, but also have VLOOKUP formlas in them, so using VB to find the bottom of the column doesn't help here, as it always finds the 400th row, unless there is a way to search for a blank value!?!?!? I do however have a column which is copyed from Sheet 1 and pasted in column P on Sheet 2, these are already values (text).

Using Offsets from column P, I can select the area for which I want to be printed (this area goes from the bottom of the table to Cell B13), but I cannot set this as the print area! I have tried coding like...
ActiveSheet.PageSetup.PrintArea = CurrentRegion
ActiveSheet.PageSetup.PrintArea = CurrentSelection
either followed by
ActiveSheet.PrintOut Copies:=1
but this still prints all 400 rows!?

Also the page setup is already set to landscape, it is on A4, and the width of the columns fits on one page.

Any help would be very helpful, i'm doing my nut in and I can't figure it!!!!

Post your answer or comment

comments powered by Disqus
I have a workbook with 25 sheets - 20 sheets are similar and require the PRINT AREA to be set the same in each sheet. I cannot group them as the Option to SET PRINT area is then not available in 2007. I have used this code but it sets the print area on all the work sheets in the workbook. I have tried to alter the code myself to only include those sheets that I have selected but have had no success.

    Dim strPA As String, Sht As Worksheet 
    On Error Goto NOT_RANGE 
    strPA = Selection.Address 
    For Each Sht In ActiveWorkbook.Worksheets 
        Sht.PageSetup.PrintArea = strPA 
    Exit Sub 
    NOT_RANGE:  MsgBox "Select the Print Area Cells, then try again!" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be appreciated.

I have a problem with workbooks from Excel 2003 that I am now using in 2007 where the print area is set to a column range and within the range there are shapes and textboxes etc. If I do a print preview (or print) then when the worksheet is displayed again the shapes and textboxes are hidden. However, if I highlight part of the worksheet that contains the shapes with the mouse I can see that the shapes are still there. If the print range is set to a range or cells rather than a column range then there is no problem. ( I need to use a column range as there is external data in the worksheet and the number of rows can vary. There are also parts of the worksheet that I do not want printed).

To illustrate this behavior : in a new Excel 2007 worksheet insert a shape and then select some columns including those that the shape appears in and then use the Set Print Area option. The shape disappears ( well it does on my pc). If you use the Clear Print Area option it becomes visible again.

Anyone any ideas what's going on or how to keep the shapes visible?

I am developing a spreadsheet which prints parts of several worksheets.
The ranges to be printed change depending on the data.
I have the ranges I want to print as text strings and so far I have used the GOTO function to select the ranges by pasting in the text and then used SET PRINT AREA, but in the macro I end up with the selected range as the print area.
That means when I run the macro, i always end up printing the range that was selected when I recorded the macro.

thanks for your help,

Please could somebody help me.

I need to set the print area in a macro. The range that I want to set the print area to is returned to a cell.

For example:

Cell B5 contains the value: $B$1:$O$50.
In the macro I have the line: ActiveSheet.PageSetup.PrintArea = ???

What do I put in place of the ??? above, to retrieve the value from B5?

Thank you!


I do not know how to code the print area in VB. I would like to set the area at the end of the cell that are blank. So if the las row is A29 and the last column is P29, the range would be A29:P29 as a print area.
Thanks for your help

Ok, let me explain the title and the problem. This isn't something that is serious or a requirement to my program, just a query that if it is availible, I will use.

I have a sheet which gets information passed to it every so often. This information is added very nicely in a table format. Every week the table gets just a little bit longer, but never any wider.

I would like to be able to print this page as well as the others, but this isn't possible at the minute. The other sheets fit on one page, whilst this page continues to get longer and longer as time goes by. Since the other pages fit on one page, a simple "Set Print Area" in the File menu will suffice, and once the margins are set, away we go.

This page that grows each week isn't so simple. I have some text in some cells after the table. Now, this can be deleted if I modify some other code I am sure, but since I haven't doen that yet it doesn't work. I would like to set the print so that it will only print the cells I want it to. The cells I want it to print however will change daily/weekly.

Here is an Example.
The table goes from column A to column J in width.
In length, the table size is undefined, due to the fact that it grows over time.

How can I make the page print from A to J wide, but as far as needed down?

in other words, I need the Print Area to change when more rows are added to the table in order for them to all be printed. I want the width to fit one page accross, but it take as many pages down as is needed.

I do hope this has explained things properly. If not, I will try again.

Many thanks in advance,

This code works for a single worksheet, but when I tried to adapt it to work for the workbook I received the following error message: "Application or Object defined error" at Set lastCell = lastCell.Offset(-1,0). I have a hyperlink in the last row so this code sets the print area to the last row -1. The reason I'm using VBA for this is because if I select all the worksheets the Print Area option becomes disabled, also because I have a large number of workbooks and not all of them use the same last row across all worksheets so for obvious reasons I'd like VBA to do the work.

     'Sets print area = to last used column and one before the last used row
    Dim lastCell As Range 
    Dim wks As Worksheet 
    For Each wks In ThisWorkbook.Worksheets 
        Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0) 
        Do Until Application.Count(lastCell.EntireRow)  0 
            Set lastCell = lastCell.Offset(-1, 0) 
        ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address 
    Next wks 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I am inexperienced with vba and have been using an if formula code to set print area on some tags on a worksheet. It has worked fine except I now need to drastically expand the number of tags. Is there another option I could use? Offset? Example follows and continues through the tags.

    If  Range (A48) . Value>0 Then 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you for any time you can give me.
Mike Jaco

How can I set the print area in excell 2007?

The "set print area" icon has been eliminated in Excel 2003. It was an
excellent feature and should be returned.

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

The "set print area" icon has been eliminated in Excel 2003. It was an
excellent feature and should be returned.

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

I know very little about Excel since until recently all of my work was in
Microsoft Word. However, people now email Excel documents to me to print and
I get more confused with each one.

Is it possible to set three different-sized print areas in one sheet, each
with a different percentage of size adjustment? There are times when many
pages of a sheet will go from A-H and the last one or two will go from A-AS.
When each sheet is set to fit on a legal size page, the A-H pages are as
microscopic as the last ones. What, if anything, could I do to even out the
appearance and readability?

I'm sure that to a seasoned user this is a stupid question. But it's been
driving me crazy!

Thanks in advance for being there.

I have already posted about this problem but unfortunately no one replied! I have got a bit further with it but again I am stuck, so thought I would plead for help again!!

I have a workbook that is made up of 5 worksheets. Each worksheet is 8 pages long. The worksheets request various info and incorporate check boxes in some of the columns.

The work book will be printed at the end of each month, but not all of the pages on each worksheet will have been used. Sometimes, perhaps only the first five lines of page one will have been completed.

The spreadsheet is going to be used by a number of people and I want to use code that sets the print area when the user prints. I want it to print the whole page if the first row has been completed.

Cell B8 is the first cell that the user will have to input in - this is the date column. Therefore, if they input anything in B8 I want the page to print. I have used code to add a customer menu and have managed to write the code which prints the whole row is cell B8, B9, B10 (and so on) is completed, but this means that when printed it could end up with just 3 lines - I want the whole page.

I have pasted the code that I have used so far below...I would REALLY appreciate it if anyone could spare the time to help.........(ps - the code i have used has been copie from other help sites! i am a novice!)

    Dim cbWSMenuBar As CommandBar 
    Dim muCustom As CommandBarControl 
    Dim iHelpIndex As Integer 
    Set cbWSMenuBar = CommandBars("Worksheet Menu Bar") 
    iHelpIndex = cbWSMenuBar.Controls("Help").Index 
    Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpIndex, Temporary:=True) 
    With muCustom 
        .Caption = "&Custom" 
        With .Controls.Add(Type:=msoControlButton) 
            .Caption = "&Print Data List" 
            .OnAction = "PrintDataList" 
        End With 
    End With 
End Sub 
Private Sub PrintDataList() 
    Dim strPrintAd As String, lRow As Long 
    With Sheets("sheet1") 
        lRow = .Range("b65536").End(xlUp).Row 
        strPrintAd = .Range(Cells(3, 1), Cells(lRow, 20)).Address 
        .PageSetup.PrintArea = strPrintAd 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Been working with excels for several years; today it's not letting me set
print area. File drop-down menu doesn't highlight that as an option so I
can't change print area. Help?

I am trying to use the code below (with the help of this newsgroup) to set
the print area of all worksheets (in my active workbook) with the name
"Misc" to the last entry in row 7 and the last entry in columns A thru AA.

It's not working as it always tries to set the print area to one
cell...which means it's gong to the "On Error Goto... No Corner" part of the
code when the function part runs.

I'm not sure how to fix it and why it's not working... so any help is
greatly appreciated !!

Sub PrintareaMisc()
'Set Print area on Misc sheets

Dim sh1 As Excel.Worksheet
Dim sh As Excel.Worksheet
Set sh1 = ActiveWorkbook.ActiveSheet
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Misc", vbTextCompare) Then
sh.PageSetup.PrintArea = Range("A1", BottomCornerMisc(sh)).Address

End If
Next 'sh

Set sh1 = Nothing
Set sh = Nothing

End Sub

Function BottomCornerMisc(ByRef objSHeet As Worksheet) As Range
On Error GoTo NoCorner
Dim BottomRow As Long
Dim LastColumn As Long
Dim br As Long
Dim i As Long
If objSHeet.FilterMode Then objSHeet.ShowAllData

BottomRow = 1
For i = 1 To 26
br = objSHeet.Cells(Rows.Count, i).End(xlUp).Row
If br > BottomRow Then BottomRow = br

Set BottomCornerMisc = objSHeet.Cells(1, 1)
End Function

Thanks in advance!!

Hi all:

Excel 2003 used to have a feature where the user can select and set the print area. I do not see this feature in Excel 2007. So, how dow I set the print area in Excel 2007? Thank you for your kind assistance.

- Ravi

Hi all,

I have this problem: I have an excel list, and I would like to add a macro that updates the Print Area. As I try to record a macro, though, using the "Selection.SpecialCells(xlCellTypeLastCell).Select" command in order to get to the bottom of the page, and then select the whole worksheet from that point upwards, what I get is a
"ActiveSheet.PageSetup.PrintArea = "$A$1:$O$1334" command, which is not really useful cause its tied to that 0:1334, which may change in the future..

please, any suggestions?
Thank you in advance

I have an ever growing list of data in range A:G with print area set as such, column B uses vlookup formula's in each cell, as well as borders all the way through & Rows $1:$1 set to repeat at top. I also have the pages numbered in my footer. Currently before printing I am manually setting the print area so as not to print blank pages, and keep my page numbers accurate. I would love to be able to assign a macro to a button to automatically do this & print the pages...Any body able to help?

this is what I have tried, which sets print area, but does not ignore formulas & cell formatting

    Dim myrange As String 
     ' Sets Range from cell A1 to column 7 (G)
    myrange = Cells(Rows.Count, 7).End(xlUp).Address 
    ActiveSheet.PageSetup.PrintArea = "$A$1:" & myrange 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have code that is as follows, is there anyway that I can use this to set the print area also?

tmp = ActiveSheet.Range("A2").End(xlDown).Row 
Range("A2:C" & tmp).Select 
With Selection.Borders(xlEdgeLeft) 
    .Weight = xlThin 
End With 
With Selection.Borders(xlEdgeTop) 
    .Weight = xlMedium 
End With 
With Selection.Borders(xlEdgeBottom) 
    .Weight = xlThin 
End With 
With Selection.Borders(xlEdgeRight) 
    .Weight = xlThin 
End With 
With Selection.Borders(xlInsideVertical) 
    .Weight = xlThin 
End With 
With Selection.Borders(xlInsideHorizontal) 
    .Weight = xlThin 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

In Excel 2007, I have a spreadsheet with a Rectangle covering some of the
cells so that when I print the worksheet, text that I add to the rectangle
will print, but the text in the cells behind the rectangle remains hidden.

As soon as I "SET PRINT AREA" to the spreadsheet, the rectangle isn't
viewable anymore, though the rectangle is still there. When I print the
spreadsheet, the rectangle successfully hides the information and the text
within the rectangle prints properly, but if I need to change that text, I
can't because I can't see the rectangle any more. I know it's there, but I
don't know how to edit the text within it.

Any suggestions?

Hello all,
Here's what I am trying to do. Somewhere in column A is the phrase "Total Package Cost". Depending on a number of things, it can be anywhere in column A so I can't set a straight cell reference. What I want to do is set the print area through page break preview to include that cell and go up to F1. So if "Total Package Cost" appears in A70, then the print area would be A1:F70.
Here's the code I have so far for this:

While ActiveCell <> "Total Package Cost"
ActiveCell.Offset(1, 0).Select
ActiveSheet.PageSetup.PrintArea = ActiveCell":$F$1" I know the last line is wrong, but I didn't know the syntax for that. I may need to define a variable. Whatever you guys think is best.

Thanks for your help!


Please help...
I have excel sheet with data in lots of columns..

I need a macro which will perform following 2 requirements:

1. When i open this sheet, it should automatically do PAGE SETUP and margins as follows :

Orientation as Landscape
 Margins : Top=0.42 

Help is greatly appreciated....

Thank You in Advance..

Riz Momin

Hi all,

Hope somebody can help me out!

I would like to run a macro to set a print area.

I need to print columns A thru S only.
I need to define the bottom row of the print area to be the last row
before column A becomes blank.

(The length of the list of data is dynamic, therefore I cannot use a
static range. I wish to omit any row below the point when column A
becomes blank, and I am unable to sort the data in anyway.)

I was thinking of using an IF statement inside a named range.

Any ideas?

Thanks in advance.

I have used this spreadsheet multiple times and have been encountering this more frequently lately.

When I go to print my document the front sheet of my 13 sheet spreadsheet resets the print area so that each cell is one page. I cannot adjust it by going through page set up and scaling it to a percentage or page 1 to 1. It doesn't do anything when I try to remove all page breaks. It doesn't do anything when I clear the print area.

I'm stumped.

What I have been doing it just making a copy of the sheet and copy/pasting all of my formulas then it seems to work for a while. I've adapted and changed the document to fit a few different jobs I'm working on and each of the documents at one time or another starts doing this.

Can anyone help me???

No luck finding an answer? You could always try Google.