Print Macro #2

Trying to write a macro to print a spreadsheet where the 
number of rows of data varies.  

What's wrong with this code?

    Dim rng As Range
    Set rng = Range("A1").End(xlDown).End(xlToRight)
    With ActiveSheet.PageSetup
        .PrintArea = rng
    End With

Thank you.
Pam
0
gasp0225 (1)
9/27/2003 6:03:29 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
604 Views

Similar Articles

[PageSpeed] 3

Hi Pam

Leave your PrintArea empty
Excel will print all cells with a value on the sheet

-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl



"Pam" <gasp0225@juno.com> wrote in message news:16e401c38521$a7d25420$3501280a@phx.gbl...
> Trying to write a macro to print a spreadsheet where the
> number of rows of data varies.
>
> What's wrong with this code?
>
>     Dim rng As Range
>     Set rng = Range("A1").End(xlDown).End(xlToRight)
>     With ActiveSheet.PageSetup
>         .PrintArea = rng
>     End With
>
> Thank you.
> Pam


0
rondebruin (3790)
9/27/2003 6:20:06 PM
When I record this I get a string assigned to .printarea 
not an object...
Try to append .address to rng before assigning it.

Stefano

>-----Original Message-----
>Hi Pam
>
>Leave your PrintArea empty
>Excel will print all cells with a value on the sheet
>
>-- 
>Regards Ron de Bruin
>(Win XP Pro SP-1  XL2002 SP-2)
>www.rondebruin.nl
>
>
>
>"Pam" <gasp0225@juno.com> wrote in message 
news:16e401c38521$a7d25420$3501280a@phx.gbl...
>> Trying to write a macro to print a spreadsheet where the
>> number of rows of data varies.
>>
>> What's wrong with this code?
>>
>>     Dim rng As Range
>>     Set rng = Range("A1").End(xlDown).End(xlToRight)
>>     With ActiveSheet.PageSetup
>>         .PrintArea = rng
>>     End With
>>
>> Thank you.
>> Pam
>
>
>.
>
0
9/28/2003 6:51:28 AM
Ron is of course right. However, you may be interested for future reference
in why your macro was not working.

As far as I can see, your set range statement just sets one cell (bottom
right of the spreadsheet).

Try this instead:-

Set rng = Range("A1", Range("A1").End(xlDown).End(xlToRight))

Geoff



"Pam" <gasp0225@juno.com> wrote in message
news:16e401c38521$a7d25420$3501280a@phx.gbl...
> Trying to write a macro to print a spreadsheet where the
> number of rows of data varies.
>
> What's wrong with this code?
>
>     Dim rng As Range
>     Set rng = Range("A1").End(xlDown).End(xlToRight)
>     With ActiveSheet.PageSetup
>         .PrintArea = rng
>     End With
>
> Thank you.
> Pam


0
9/28/2003 9:13:19 PM
In fact, unless you need rng for later, why not simplify it to this:

ActiveSheet.PageSetup.PrintArea = _
Range("A1", _
Range("A1").End(xlDown).End(xlToRight)).Address

Regards

Geoff


"Pam" <gasp0225@juno.com> wrote in message
news:16e401c38521$a7d25420$3501280a@phx.gbl...
> Trying to write a macro to print a spreadsheet where the
> number of rows of data varies.
>
> What's wrong with this code?
>
>     Dim rng As Range
>     Set rng = Range("A1").End(xlDown).End(xlToRight)
>     With ActiveSheet.PageSetup
>         .PrintArea = rng
>     End With
>
> Thank you.
> Pam


0
9/28/2003 9:26:41 PM
Use this then Pam, GF

Sub test()
    With ActiveSheet.PageSetup
        .PrintArea = ActiveSheet.UsedRange.Address
    End With
End Sub


-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl



"GB" <ThisIsNotMyEmailAddress@Anywhere.InTheUniverse> wrote in message news:bl7itf$1ar$1@hercules.btinternet.com...
> Ron is of course right. However, you may be interested for future reference
> in why your macro was not working.
>
> As far as I can see, your set range statement just sets one cell (bottom
> right of the spreadsheet).
>
> Try this instead:-
>
> Set rng = Range("A1", Range("A1").End(xlDown).End(xlToRight))
>
> Geoff
>
>
>
> "Pam" <gasp0225@juno.com> wrote in message
> news:16e401c38521$a7d25420$3501280a@phx.gbl...
> > Trying to write a macro to print a spreadsheet where the
> > number of rows of data varies.
> >
> > What's wrong with this code?
> >
> >     Dim rng As Range
> >     Set rng = Range("A1").End(xlDown).End(xlToRight)
> >     With ActiveSheet.PageSetup
> >         .PrintArea = rng
> >     End With
> >
> > Thank you.
> > Pam
>
>


0
rondebruin (3790)
9/28/2003 9:27:49 PM
Oops  GB it is

-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl



"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:ulGEgdghDHA.3616@TK2MSFTNGP11.phx.gbl...
> Use this then Pam, GF
>
> Sub test()
>     With ActiveSheet.PageSetup
>         .PrintArea = ActiveSheet.UsedRange.Address
>     End With
> End Sub
>
>
> -- 
> Regards Ron de Bruin
> (Win XP Pro SP-1  XL2002 SP-2)
> www.rondebruin.nl
>
>
>
> "GB" <ThisIsNotMyEmailAddress@Anywhere.InTheUniverse> wrote in message news:bl7itf$1ar$1@hercules.btinternet.com...
> > Ron is of course right. However, you may be interested for future reference
> > in why your macro was not working.
> >
> > As far as I can see, your set range statement just sets one cell (bottom
> > right of the spreadsheet).
> >
> > Try this instead:-
> >
> > Set rng = Range("A1", Range("A1").End(xlDown).End(xlToRight))
> >
> > Geoff
> >
> >
> >
> > "Pam" <gasp0225@juno.com> wrote in message
> > news:16e401c38521$a7d25420$3501280a@phx.gbl...
> > > Trying to write a macro to print a spreadsheet where the
> > > number of rows of data varies.
> > >
> > > What's wrong with this code?
> > >
> > >     Dim rng As Range
> > >     Set rng = Range("A1").End(xlDown).End(xlToRight)
> > >     With ActiveSheet.PageSetup
> > >         .PrintArea = rng
> > >     End With
> > >
> > > Thank you.
> > > Pam
> >
> >
>
>


0
rondebruin (3790)
9/28/2003 9:32:10 PM
Reply:

Similar Artilces:

Worksheet slow after printing
I have a worksheet with a number of command buttons to execute VBA commands. Everything runs fast until I print the worksheet--then everything is in slow motion. I do not have to run any code. I can enter data manually & then print--then it even takes a long time to delete the data. Would be happy to send file. -- Jim Morris Do you have any Workbook_BeforePrint code. Press Alt F11 and look at the code in the MyWorkbook module? "jm100" wrote: > I have a worksheet with a number of command buttons to execute VBA commands. > Everything runs fast until I print th...

How to incorporate 2 sets of changing named ranges
Hi All I'm a novice that needs help please..... sorry - I've tried but I'm stuck.... (one workbook, heaps of worksheets) I've created a macro that runs a set of actions for multiple named ranges by calling each (see code below) It finds / copies / pastes data from different worksheets & named ranges into an overview. The macro adds a 1 or 2 or 3 to "collectionMT" and repeats the actions against each named ranges: CollectMT1, CollectMT2, CollectMT3, CollectMT4 etc through to CollectMT9 I don't know how to do the same (in the same macro) for t...

"last line of receipt printing on first line of next receipt"
I have adjusted the .xml file for the receipt countless times and have downloaded .xml templates from the Customer Source. Every time I print a receipt the last line is cut off and inserted as the first line on the next receipt. Any ideas for fixing this? Thanks! What Printer/Driver combination are you using? I would guess that you're using some variety of Windows Driver, and that the problem has to do with the page size defined there rather than with the Receipt Template... -- -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ------------------...

Countif #2
Hi My knowledge of using excel formulas is limited to basic cals, but I need to develop it quick! I have a spreadsheet that is growing daily (currently about 350 records) and I need to count the number of entries by the first two digits of the postcode (zip code). I guess countif is the right expression to use, but how do I ask it to only look at the first two digits of the cell Thank you for your help Hi some ways: =COUNTIF(A1:A100,"12*") if '12' are the first two digits of your zip code or try =SUMPRODUCT(--(LEFT(A1:A100,2)="12")) -- Regards Frank Kabel Fra...

Print View or Print Paper No start time or end time
How come when i print view or print hard copy of my monthly calendar I dont see the start and end time? "dearcc" <dearcc@discussions.microsoft.com> wrote in message news:0B8CACE7-6A38-4652-8010-B92713583CAE@microsoft.com... > How come when i print view or print hard copy of my monthly calendar I dont > see the start and end time? Outlook version? -- Brian Tillman [MVP-Outlook] They only show if the cell is wide enough. Try Landscape format. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solut...

how can i print the notes of powerpoint presentation slides?
i want to print my notes so i have them in front of me (not on the comp screen), but if i print the powerpoint presentation, only the slides are printing..... please help! On the print menu, there is a field called, "Print what:" wherein you can select "Notes Pages". "Luisa Grey" wrote: > i want to print my notes so i have them in front of me (not on the comp > screen), but if i print the powerpoint presentation, only the slides are > printing..... > > please help! ...

Backgrounds in Excel #2
I want to be able to print the background in excel, and also when I PDF an excel file the background dissapears. How can I make it to print/view. Is there any way. Please help me. If there's a way to do it, would you please email me the steps to make it work. Thanks, Bianka ...

Copying a form to the same sheet using macro
Hi, Hope I can clearly demonstrate the problem=85..I am not so good in these=85. In =93Jan =9110=94 sheet I have created a form to capture the sales for wee= k 1. If I want to copy Week 1 sale and make another form in the same sheet using macro=85..how do I do that. When I copy week 1 form to week 2=85..next I want to copy week 2 to week 3 by clicking a command button and I would like to do this using the same code. Next problem is, I want the summary to add up in a =93Summary=94 sheet. Is it possible to add up totals as I include the new forms (i.e. week 2, week 3)? If someone...

How to display a macro generated menu in Excel 2007
I have an addin that was written using Excel VBA 2003 and works fine. It puts a new item in the menu bar just before "Help" Now the addin is being used in Excel 2007 and it is clear that the macros are running, but we cannot find out how to get its menu item to display anywhere. I guess this is more of an Excel 2007 question than a VBA question, but any help would be much appreciated! Thanks! custom menu items go to add-ins tab in excel 2007. but i think you cannot control where it go on add-in tabs <NoSpam@aol.com> wrote in message news:b988v519kbcelnivj...

Zooming in chart by 2 click ??
Hello all, Is there anyone who can tell me if it is possible to zoom in a chart by clicking 2 points on that one. Let's say a have a chart from where axis is from 01/01/2005 to 01/01/2006 with 1 point per day (365 points). Can i click 1 point - tht will be the entry point - and click an other one that will be the end of the zoom? Thanks for your help Herve. -- herve ------------------------------------------------------------------------ herve's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27314 View this thread: http://www.excelforum.com/showthread.p...

Send Internal Mail Externally #2
I have a subsidiary that we connect to through a vpn tunnel. They are not a part of our Exchange organization. They use lotus notes for email. We are unable to telnet into their server and we believe that for some reason port 25 is being blocked internally. Is there any way to ensure that email to this subsidiary travels externally rather than internally? We are running Exchange 2003 sp2 on W2K3 server sp1. There is no lotus notes connector of any kind. All email directed to them is sitting in the queue. Any help would be appreciated. Thanks. CK Hello Chris, You can create an SMTP c...

Office 2004 Service Pack 2 on CD
I was wondering if Office 2004 Service Pack 2 will be available on cdrom. If so, can you estimate when? This service is available to Office 2003 customers, so I was wondering if it will be made available to Macintosh customers as well. http://www.microsoft.com/office/orkarchive/o2k3cd.htm Thank you. Brandon Moses moses388 AT hotmail DOT com I've never paid attention to this, but as far as I know, they don't do that for MacOffice. If they did, the info would be somewhere here: http://www.microsoft.com/mac/default.aspx If you have a friend with broadband, they might be able to do...

macros #17
How do I hide my macros so another user cannot view them? Thanks D, You can use Tools - VBA Project Properties - Protection - Lock project for viewing. You can supply a password for unlocking. This isn't terribly secure, but it will keep the honest people out. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "drpence" <anonymous@discussions.microsoft.com> wrote in message news:8B665ED4-0862-43F6-9390-5634BC6473CB@microsoft.com... > How do I hide my macros so another user cannot view them? Thanks That will ...

Report Options #2
Hi, What is the maximum reports options can I create in GP? Cheers Hi Kbres, Each report in GP can have a maximum of 32 different report options based on my past experience and have not tried creating beyond this number in GP 10. Hope this helps! Cheers, Dennis Araullo, MACS MCTS GP,CRM,AX MCTIP Database Administrator 2008 CCEA Thanks a lot! A Dexterity drop-down list control can display up to 32,767 items. So you should be able to have up to that number of report options for any given report. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http:/...

annoying updating time! #2
Ok the first solution posted worked for me. I want a time stamp for a LOT of cells. the scenario is student give their ID number. they type it in and i the next cell it stamps the time. bearing in mind there could be hundred students. So do i put a range in the ""? at teh moment its "b3 do i enter "b3-B1003" ?? thanks so far guys/gal -- AJSulliva ----------------------------------------------------------------------- AJSullivan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1652 View this thread: http://www.excelforum.com/showthread...

CRM 4 Printing Workflows
We are using CRM4 and have created a number of workflows. I was just wondering if there would be any way to print these workflow, or create a document showing the steps of the workflow? This would just be very useful to save me alot of documentation time. Apart from a screen shot no printing option is available. Technically an application could be build to do this but I am pretty sure that there is nothing out there at the moment. -- Patrick Verbeeten (MCPD) Lead Developer Aviva IT Extended Entity and Plug-in browser: http://www.patrickverbeeten.com/maps/CrmTool.aspx "Rob...

How can i add a print-and save-date field in Excel as in Word?
In word, you can add a Field of the print-date and save-date. I want to do the same on an Excel document ...

Sales Transaction completes but receipt won't print
I had my system setup to use a windows driver for my TM-T88iii printer with RMS with RMS 1.2. The sales transaction would complete but the receipt would not print until I exited Store Operations. I have switched back to an OPOS driver for the printer; however this is really not desired since I would like to have access to the windows fonts for receipt formatting. Any ideas on how to make this work? Can I use the windows driver for the printer? Thanks, Mark You probably don't want to do that -- SLOWWW printing unless you use the native fonts of the printer (like OPOS does). Ma...

pivot chart formatting #2
Hi there, I've a sheet with a cake pivot chart. I customized different parts of the chart (colour of characters, data format in perc.,etc). When I update the pivot table connected, the chart loses my previous setting. Is there any trick to solve this problem? Thank you in advance. Renato Hi, This is a known problem. See KB article. Changing a PivotChart Removes Series Formatting http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q215904 Cheers Andy Renato wrote: > Hi there, > I've a sheet with a cake pivot chart. I customized > different parts of the chart (col...

Pivot Table Settings #2
Hello, Is there a way to have the Pivot table fields set to sum (or any other calculations)instead of count before you start to create? Thank you, Denise ...

updates #2
After downloading an update for office, I am unable to use it on more than one machine at a time (laptop and desktop). I am still only one user. Is this in violation of the lic. agreement, or is it a flaw that can be fixed? On 6/9/06 15:56, in article 1157554575.148285.77330@b28g2000cwb.googlegroups.com, "aQ4uMMn" <QMont1@gmail.com> wrote: > After downloading an update for office, I am unable to use it on more > than one machine at a time (laptop and desktop). I am still only one > user. Is this in violation of the lic. agreement, or is it a flaw that > can b...

Macro Help #5
Dear Experts I have a range in a spreadsheet set up with a macro that concatenates the row and column headings and copies the number (if it is >0) in the cell where the column heading and row heading meet to a new worksheet. A bit like looking up a street directory. Sometimes I have to change the criteria and want only numbers copied that are >10. I know how to change the macro to do this. I now have to copy numbers that are > than minus 1. Because some of the cells in the range are empty it still concatenates the empty cells the row and column headings and copies them to the workshe...

Print Address Labels
Have an Excel spreadsheet with Name, Address, State, Zip in different columns. Are there stored functions an easy method to print envelopes or at least write that information to a text file for printing as address labels? Al Are you attempting to make labels directly in Excel? It could be done in Excel only, but........ Investigate the use of Word's mailmerge feature to get Excel data to labels and envelopes. Doing it strictly in Excel is difficult and involves much trial and error formatting. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMer...

Publisher 2003 Banner Printing error
I just purchased Publisher 2003 and want to print some banners, but the print is different to what is on my moniter screen. When I had Pulisher 2002, everything was fine, but with the Publisher 2003 in my system, the second I click the print button, what I see on my monitor suddenly enlarges way beyond the top and bottom of the page, resulting in a print I cannot use. Can someone out there please help me with this error? Have you actually printed the banner? What does it look like in print preview? You might want to look into an updated printer driver. Go to the manufacturer's web ...

Save Mword Macro?
How do I get a Microsoft Word macro to save for future use in other documents? -- Losse ------------------------------------------------------------------------ Losse's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24813 View this thread: http://www.excelforum.com/showthread.php?threadid=386529 I realize this is normally an Excel forum, but can anyone help me wit this basic bit of information for Word -- Loss ----------------------------------------------------------------------- Losse's Profile: http://www.excelforum.com/member.php?action=getinfo&...