 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2006Print Area
Hi I have data constantly changing from using 100 to 1500 lines and when it comes to printing, I have to constantly set the print area or drag the print preview handles. Is there a way that a piece of code can find the last occupied cell in a column and have the page set up change accordingly? Many thanks in advance John John, With ActiveSheet .PageSetup.PrintArea = .UsedRange.Address End With NickHK > Hi > [quoted text clipped - 6 lines] > > John Hi Nick, thanks for your help. I have tried it, but it doesnt work, maybe because I have formulas that run down each side of the data that I want printed!! John > John, > With ActiveSheet [quoted text clipped - 14 lines] > > > > John Hi John, With ActiveSheet .PageSetup.PrintArea = intersect(.UsedRange.Address,range("a:b")).address End With Change "a:b" to be columns you want printed. Regards, Ivan Hi Ivan - again It comes back as a run time error - object required Any ideas ? John > Hi John, > [quoted text clipped - 7 lines] > Regards, > Ivan Hi John, sorry, should be: intersect(.UsedRange,range("a:b")).address Regards, Ivan Hi Ivan, That worked, but I still have a problem and I think it has something to do with my range's The area I want to print is a pivot table along with some formulas that run alongside, and because I need the formulas to refresh, I put them into a range that goes up to 1500 lines. As an experiment I shrunk the range down to 500 lines, ran your code and walla the print area ended on the 500th line. John > Hi John, > [quoted text clipped - 3 lines] > Regards, > Ivan Hi John, try to reset last used cell with this: Dim x As Long x = ActiveWorksheet.UsedRange.Rows.Count 'and continue with setting printarea With ActiveSheet .PageSetup.PrintArea = intersect(.UsedRange,range("a:b")).address End With Regards, Ivan Sorry Ivan - being a pain again. I tried it and got the run time again with Object Required John > Hi John, > [quoted text clipped - 10 lines] > Regards, > Ivan Hi John, what line? Regards, Ivan Stops on this: x = ActiveWorksheet.UsedRange.Rows.count John > Hi John, > > what line? > > Regards, > Ivan Hi John, rearange the code like this: Dim x As Long With ActiveSheet x = .UsedRange.Rows.Count .PageSetup.PrintArea = intersect(.UsedRange,range("a:b")).address End With Regards, Ivan Hi Ivan, thanks for your patience, but I think your going to give up on me, because now I am back to where I was before. I entered your latest code but the page setup still goes down to the end of the range and bypasses all the data. John > Hi John, > [quoted text clipped - 8 lines] > Regards, > Ivan Hi John, let's try different attitude: dim i as long dim j as long dim rng as range set rng=range("b1..e1") ' the address of first row of data you want to print out j=0 for i = 1 to rng.columns.count j=worksheetfunction.max(j,rng.cells(application.Rows.Count-rng.Row,1).end(xlup).row) next i activesheet.pagesetup.printarea = rng.Resize(j-rng.row+1,rng.Columns.Count).Address Change "b1..e1" in "set rng=range("b1..e1")" to be address of first row of data you want to print. Please let me know if it worked. Regards, Ivan Fantastic - Ivan you are a star. Many thanks - much appreciated - and thanks to Nick Take care Regards John > Hi John, > [quoted text clipped - 19 lines] > Regards, > Ivan Using the example posted: Assume your pivotTable is in B1:D200 and you have formulas pre-entered in E1:E1500 this code only prints out B1:D200? Or are you not printing out the pre-entered formulas? (Pivot Table actually in B1:E200 and formulas in F1:F1500 as an example - then rng = Range("B1:E1")) Just curious - because I don't see how this solves the problem you described if you want to include the pre-entered formulas.  SignatureRegards, Tom Ogilvy > Fantastic - Ivan you are a star. > [quoted text clipped - 29 lines] > > Regards, > > Ivan Hi Tom, Thanks for your input. I wanted to print out some of the columns that contained formulas as well as the pivot table and I think the line that contains (application.Rows.Count-rng.Row,1) looks at the data in the first column (ideal because of no formulas) and gives me what I am looking for. However, I have tried to apply the same principle on a different page, but this time not so easy and I think it’s because all the columns have formulas. This is my poor way of trying to work around the problem: I am trying to use code to enter a value into a different column so that Toms code can do the same trick: Range("E40").Select If ActiveCell > 0 Then Range("N40").Select ActiveCell.FormulaR1C1 = "1" Range("E41").Select If ActiveCell > 0 Then Range("N41").Select ActiveCell.FormulaR1C1 = "1" Range("E42").Select If ActiveCell > 0 Then Range("N42").Select ActiveCell.FormulaR1C1 = "1" And so on and so on (I need 50 lines done this way) I know you must be laughing at me right now, but can you see what I am trying to do? May I ask for your help? Either there is another way altogether or can the above code be shortened somewhat? Regards John > Using the example posted: > [quoted text clipped - 41 lines] > > > Regards, > > > Ivan oops sorry - I meant Ivan's code > Hi Tom, > Thanks for your input. [quoted text clipped - 79 lines] > > > > Regards, > > > > Ivan Yes, your right. That whole loop ends up just giving you the used last row in column A from what I can see. Not sure why the loop is even there. SignatureRegards, Tom Ogilvy > Hi Tom, > Thanks for your input. [quoted text clipped - 79 lines] > > > > Regards, > > > > Ivan Possibly for i = 1 to 50 if cells(i + 39) > 0 then cells(i + 39,"N").Value = 1 Next if you want to quit the first time the cell is not > 0 then for i = 1 to 50 if cells(i + 39) > 0 then cells(i + 39,"N").Value = 1 else exit for end if Next SignatureRegards, Tom Ogilvy > Hi Tom, > Thanks for your input. [quoted text clipped - 79 lines] > > > > Regards, > > > > Ivan John, I'm sure Ivan will see it, but remove the .address from .usedrange. NickHK > Hi Ivan - again > [quoted text clipped - 15 lines] > > Regards, > > Ivan Hi Nick, thanks, you're right. And I already corrected myself (probably the post was not visible to you as it takes some time to display) Regards, Ivan |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.