Programmable Print Area: Macro To Set The Print Area According To The Amount Of Data In A Particular Range Of Cells

Feb 25, 2009

Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction

View 2 Replies


Excel 2003 :: Programmable Print Area

Feb 25, 2009

Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells.

I find I can include this instruction :

[Code] ....

in a macro to set the print area to the currently required fixed range of ‘B1’ to ‘D210’.

The problem arises when wanting to alter the ‘210’ by introducing a string variable, say

PrintRange = "$B$1:$D$" & UpperLimit

where ‘UpperLimit’ is the top row to be included in the print area.

View 4 Replies View Related

How To Set Fixed Print Area In Excel So Cells Within Print Area Can Still Be Modified

Feb 12, 2013

I have a workbook with multiple sheets that I need to print.

Is there a way to create a fixed print area within which you can mess around with formatting without extending or shrinking the print area?

I have tried adjusting the margins settings so that they are all the same, but this does nothing to keep a fixed print area.

View 1 Replies View Related

Print 2 Selected Area (highlighted Area)?

Apr 11, 2013

How can I print 2 selected area (highlighted area). I know there is a trick to do that. Right now I can only select (highlight) one area to print, but would like to know how to print 2 or more areas at same time

View 7 Replies View Related

Macro To Print Active Area Excluding Cells With Forumula That Doesn't Return Value

Dec 20, 2007

Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints !

Is this possible?

FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells.

I used this code from a post on a similar topic, but excel is complaining code
in bold

Private Sub Print_Area_Click()
Dim lastCell As Range
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
End Sub

View 9 Replies View Related

Print Previewed A Worksheet And Find A Tiny Image With A Small Portion Of The Print Area

Mar 27, 2009

I just converted from Excel 2003 to 2007 and print previewed a worksheet and find a tiny image with a small portion of the print area. When I look at the sheet in page break preview mode, I see the 8.5 x 11 sheet broken up into approx 77 smaller sheets. When I try to move the page breaks to include the whole sheet it states the change cannot be made as it will result in an image less than 10%. I have attached two images - the first shows the print preview I get, and the second show the multiple pages when I view in page break.

View 4 Replies View Related

Set Print Area To Used Cells

Apr 20, 2007

I am trying to write a macro that will allow the user to print out the sheet from the beginning to the last used row. The catch is that I have formats and formulas all the way to the end (A1:O319). My basic format is a frozen Pane (for a header and summary) from A1:O18, then the user inputs their data starting on B19. The columns never change, and the rows will be variable. I tried a dynamic range to no avail, and have most recently tried this

Sub printter()
Dim myrange
Dim Rng, cell As Range
Set myrange = Range("A320").End(xlUp)
Set Rng = Range("A1:" & myrange.Address)
For Each cell In Rng
If cell = "" Then
myrange = cell.Address
MsgBox cell.Address
ActiveSheet.PageSetup.PrintArea = "$A$1:O" & cell.Row
Exit Sub
End If
End Sub

My next plan would be to write to check to see the last used cell in column B, since that cell has only formating, no formula, and is a required entry cell

View 9 Replies View Related

Set Print Area From Selected Range

Jul 12, 2006

I have some code which selected a certain amount of columns depending on
whether they contain values or not. Once I have this range set and I select
it I want to set this as the print area. I am not quite sure how to do
this. for example I tried:

ActiveSheet.PageSetup.PrintArea = ActiveCell. CurrentRegion.Address

View 9 Replies View Related

Set Print Area Of Dynamic Range

Dec 17, 2006

I have a sheet with a top portion like a fax cover letter.
Starting at row 17 I merged from B thru L.
This is where the user will type in a message.
The message can extend several pages long, so I merged about 100 rows same as 17.
My sheet only will extend to column N.
There may be instances where the user may leave several rows blank in between what they type.

I am looking for a way to set the print area from A1 to the last row of data and not the last row with formatting.

I have a command button the user is supposed to click when they want to print the sheet.
This is acceptable, but if there was a way around that it would be better.

I do have code in the sheet that will automatically adjust the row height in the merged cells if the data gets too wide. I do not want to upset that.

I have just recently started to learn VBA, so my knowledge of the various commands and syntax is limited.

This is what I have right now that doesn't work.

Private Sub CommandButton2_Click()
Dim LastCell, DataCells As Range
Dim LastDataRow As Integer
Set LastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
Set DataCells = ActiveSheet.Cells.SpecialCells(xlconstants)
LastDataRow = DataCells.Cells(DataCells.Cells.Count).Row
ActiveSheet.PageSetup.PrintArea = Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(LastDataRow, LastCell.Column)).Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

View 9 Replies View Related

Macro To Set Print Area

Jul 20, 2007

Is there a macro that can automatically set the print area by determining what the used range is. For example, If P96 is my last used cell, I would want a1: P96 as my print area.

View 9 Replies View Related

Using Macro To Set Print Area

Oct 11, 2007

My macro selects cells containing data - then i want the macro to set the print area to those selected cells - works fine - but the selected cells won't be the same each time i run the macro. Here's how it looks now:

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$M$76"

The next time i run the macro the selected cells won't be A1:M76 - how do i have the macro set the print area to whatever is selected?

View 9 Replies View Related

Print Area With A Macro

Apr 10, 2009

I am looking to set the print area with a macro, below is what I had hoped would work. It does not. I get unable to get Vlookup property of worksheetFunction class error.

Sub Printzed()
Start = Range("C6").Address
Finish = Res = Application.WorksheetFunction.VLookup(I5, Range("prlu"), 2).Address

myPrintArea = Start & ":" & Finish
ActiveSheet.PageSetup.PrintArea = myPrintArea
End Sub

View 9 Replies View Related

Print Area Range Based Upon Formula

Feb 26, 2012

I am trying to dynamically set a print area based upon a formula from the contents of 2 cells. What I need is the print area range to be C1 to Ix, where x is the result of ((A2/B2)+2).

View 2 Replies View Related

Non-contiguous Print Area Macro

Mar 18, 2009

I have a document which will require signatures after printed and want to keep the signature portion at the bottom of the page (currently rows 102-104). The problem is that the document will not need to be that long for every person and was wondering if it would be possible to have a macro that checks (C7:C:100), stops printing when it reaches the blank cells, and then prints those last 2 rows?

View 8 Replies View Related

Adjust Print Area Macro

Jun 13, 2009

The macro below works perfectly except for one problem. I only want it
to set the print area from Column A - Column M . It currently selects data i have in Columns N and beyond and i do not need that to print. From A - M is 13 columns.

View 4 Replies View Related

Limit Print Area To Cells With Values

Jun 8, 2007

In the range A1:H89 I have expanding and retracting values - all of which is determined by the numbers in column A. Column A is simply A1+1 copied down. The value of A1 is determined by the value of another cell, based on age. If the value in column A exceeds a specific figure (let's say 50), formulas in columns B - H result in answers of "" (this is "nothing"?). Assume that the value of 50 is reached in A20. All cells from A21 (inclusive) down, including B21 - H21, will have the values "" in them and show up as nothing on my screen. When I print, I'd like to only print those cells where data is, i.e. cells A1:H20.

Sub Drukwerk()
With ActiveSheet.PageSetup
.PrintTitleRows = "$7:$9"
.PrintTitleColumns = ""
End With
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = "A1:H" & LastRow
' ActiveSheet.PageSetup.PrintArea = "$A$1:$H$89"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "".......................

View 4 Replies View Related

Set Print Area For Range Bounded By Row & Column Header

Dec 4, 2007

I want to select a range in a spreadsheet and then set it as the print area. the last column I want to be selected is contains the text data "ABC" in row 1 and is the only column with data "ABC" in row 1. The last row I want to select contains the text data "DEF" in column 1 and is the only row in column 1 containing the data "DEF". Basically I want to select the range [A1:column("ABC")row("DEF")] and then set it as the print area.

View 2 Replies View Related

Macro To Select An Area, And Print Selection

Mar 15, 2009

I need a macro to select the area within the blue invoice sheet box, so everything within the blue invoice sheet box is selected , and then the selection should be printed. I will have hundreds of these invoice slips made, right below each other, i was wondering once this is done, is there an easier way to to have each invoice selected, instead of making a custom button for each sheet ( which selects just that invoice , and prints that selection ).

( Column m through v, starting at row 2 stoping at row 68, is the selection required in this example, everythign within the blue box. ).

View 13 Replies View Related

Macro To Set Print Area Based On Cell Value(s)

Dec 8, 2011

I'm looking for a way to set the print area based on the value of a cell. I have a worksheet that prints a 12-page document, with data pulled from separate sheets. At the bottom of these twelve pages is an additional 2-page section that I only need in certain instances.

For example, if a cell (let's call it A1) on Sheet1 says "brown," I need the print area set to include these additional pages. If that same cell ('Sheet1'!A1) reads "yellow," however, I don't need to include the 2 pages in the print area.

View 1 Replies View Related

Macro To Print Page Setup Area

Apr 2, 2014

My macro "prints" (saves) my sheet as Pdf-file.

I wish to improve this such as print/save is based on the active area I choose at the time.

Now I must hide a lot of columns (show only the ones I will print) before "printing":

ActiveSheet.PageSetup.PrintArea = "$A:$Z"

I just want to select some columns and set this range as a print.setup area.

View 2 Replies View Related

Macro To Align Shapes To Print Area

May 29, 2014

I have a report that has a set template except that the number of columns change.

I have 3 rectangular shapes with text boxes inside each rectangle. The Shapes take up space vertically between row 8 and 15. In some reports the columns will change, the size of columns might change due to longer text. Is there a macro that will select all the shapes and text boxes and center them column wise between the print area?

View 1 Replies View Related

User Defined Print Area Macro

Jun 25, 2008

I am looking to create a macro that will ask the user to define the row number of the beginning and ending locations that they would like to print. The columns are fixed in that the first starting row will be from column A and the ending row will be column M.

I have built a pipe tally used in the oilfield that keeps track of large amounts of pipe figures but very often is the case that there is the need to print a section of the tally data in reference to a certain depth. With the vast knowledge base located here I was able to build the first macro that prints the whole active tally but now I need something more refined.

Below is one of the copies of the macro that I was trying to get to work and it is missing a couple of things but the most important part shows up and that is how to get the inputbox variables into the defined print area.

Sub TallyVariable()
Dim StartRow As String
Dim EndRow As String
StartRow = InputBox("Please Enter Starting Row you would like to print")
EndRow = InputBox("Please Enter Last Row you would like to print")
ActiveSheet.PageSetup.PrintArea = "A" & StartRow.Address:"M" & EndRow.Address
End Sub

View 9 Replies View Related

Macro To Set Print Area W/o Hidden Rows

Jun 28, 2008

We run a small gardening shop and use a simple spreadsheet to track of various things.

Row A contains headers, with the data following in the rows underneath. This table is now quite large, and we therefore hide 600 or so rows so that only rows with data from the last week is shown. We often need to print this for easy reference. I currently do this by highlighting the area I want and setting it as print area. The print therefore doesn't include hidden cells, which is what I need.

The problem we have, therefore, is that we have to manually select this print area each time. My experience with macros is VERY limited, to the point where I can record one which will select the print area and print. However, as the list gets longer and more rows are hidden the range obviously needs to change, and my simple macro will not keep up.

So my question is as follows. Is there a simple macro I could write to assign to a button that could "keep up" with the moving range? So either it always prints the header row and the, for example, 50 rows beneath (exlcuding the hidden ones), or, even better, it prints the header row and all rows with data in them that are relevant to the last week. Each row has a cell for the date it concerns so assume this may be possible?

View 9 Replies View Related

Delete Any Data Not On Print Area

May 29, 2014

Any macros for deleting any data not on the print area.

View 3 Replies View Related

Setting Variable, Discontinuous Print Area In A Macro

May 29, 2009

I need to write a macro to set the print area to the first two columns (A & B), and the last 12 columns (the last column may change). In both cases I need to print all rows (start is row 1, last row is variable). I am trying to achieve something like the "Freeze Pane" effect with the printer. The first two rows contain column headers (dates). The first two columns contain information that needs to be included on the printout, whilst the last 12 columns contain the most recent data.
Various cells withing the selected ranges may be blank, but no row or column will be entirely blank.

View 2 Replies View Related

Macro - Print Excel Area As PDF And Attach To Mail

Dec 4, 2012

I would like to send a Excel printing area to a mail recipient by using a macro that:

2) Save the printing area as a pdf - file - use a temporary filename
1) Opens MS Outlook mail
3) Attach the temp-file to the mail

View 6 Replies View Related

Macro To Publish Print Area From Active Sheet To Web Page

Feb 26, 2009

I am trying to write a macro that publishes the print area as a non-interactive web-page to a file that sits in the same location as the spreadsheet from which the macro is being run.

I am using the the following macro:

View 14 Replies View Related

Vb Code To Set Print Area And Print Out

Aug 24, 2007

with creating vb code that will select certain rows in a spreadsheet, set the print area to those rows and print them out, and then reset the print area back to default.
The data is all on one sheet, but is split into 5 different areas, each below the next, with the column headers at the top of each.



and so on. At the press of a button it'll set the print area to the first section and print (the section includes the headers as well as the data). Then at the press of another button it'll set the print area to the next section. There could be any amount of rows of data in each section so it needs to cope with that.

View 9 Replies View Related

Create Macros To Print Area With Data Using Different Cell Format To Fit In One Page?

Feb 6, 2014

I have a calculator and I am trying to set a macro that will take the cells highlighted copy them to another sheet, change the format to standard( no background color) make the data fit in one page and print it.

this is what I got so far:

Sub Set_Print()
' Set_Print Macro
' set printing area and print


View 3 Replies View Related

Print Only Used Area

Nov 9, 2006

I have a sheet that will be about 5 pages long if printed. To minimize wasted ink and paper, using VB I would like to be able to have it only print the portion uses (data entered) along with a section at the top and bottom. The section at the top would print on the top of the first page and the section at the bottom would print on the last page.

I know I could record a macro to select an area and print that but it would not do what I am trying to accomplish.

View 14 Replies View Related

Copyrights 2005-15, All rights reserved