Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically setting Print Area
I have a spreadsheet that has a fixed number of rows, but
a variable number of columns that may contain data. I am trying to set the print area depending on how many columns have data. I have a looping structure in my code which moves through the columns and locates the last column containing data. This loop works correctly, but it returns a number. The printarea wants the R1C1 cell notation. I could preset my print area but more than likely in 90% of the cases, only half of the available columns or less will be used. I don't want to print the whole spreadsheet unless I need to so the printouts will be easy to read. I have tried using the Activecell.Address property and other methods to no avail. I am sure it is just a syntax problem, but I am not finding any good examples in the Helpless Help! Help! Thanks in advance. Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically setting Print Area
assume the lastcolumn is contained in the Variable
vCol change 25 to the number of rows. activesheet.PageSetup.printArea = Range("A1",Cells(25,vCol)).Address(External:=True) -- Regards, Tom Ogilvy "Kevin" wrote in message ... I have a spreadsheet that has a fixed number of rows, but a variable number of columns that may contain data. I am trying to set the print area depending on how many columns have data. I have a looping structure in my code which moves through the columns and locates the last column containing data. This loop works correctly, but it returns a number. The printarea wants the R1C1 cell notation. I could preset my print area but more than likely in 90% of the cases, only half of the available columns or less will be used. I don't want to print the whole spreadsheet unless I need to so the printouts will be easy to read. I have tried using the Activecell.Address property and other methods to no avail. I am sure it is just a syntax problem, but I am not finding any good examples in the Helpless Help! Help! Thanks in advance. Kevin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically setting Print Area
Tom,
Thanks! I figured it out from an answer Ron de Bruin gave Pam eralier today. I used: Worksheets(1).Range(Cells(1, 1), Cells(52, curCol)).PrintOut (Excuse wordwrapping) I want to thank everyone who helps answer questions on this forum! I learn a lot from reading answers you all give to other people as well as from my own posted questions! Thanks Again! Kevin -----Original Message----- I have a spreadsheet that has a fixed number of rows, but a variable number of columns that may contain data. I am trying to set the print area depending on how many columns have data. I have a looping structure in my code which moves through the columns and locates the last column containing data. This loop works correctly, but it returns a number. The printarea wants the R1C1 cell notation. I could preset my print area but more than likely in 90% of the cases, only half of the available columns or less will be used. I don't want to print the whole spreadsheet unless I need to so the printouts will be easy to read. I have tried using the Activecell.Address property and other methods to no avail. I am sure it is just a syntax problem, but I am not finding any good examples in the Helpless Help! Help! Thanks in advance. Kevin . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically setting Print Area
That will print your range, but it won't set the printarea (what you asked).
However, it you are happy, I'm ecstatic. If you are going to qualify Range, then you should qualify all references With Worksheets(1) .Range(.Cells(1, 1), .Cells(52, curCol)).PrintOut End With otherwise don't qualify anything (and thus all references refer to the sheet containing the code (if in the sheet module) or to the activesheet (if in a general module). Range(Cells(1, 1), Cells(52, curCol)).PrintOut -- Regards, Tom Ogilvy "Kevin" wrote in message ... Tom, Thanks! I figured it out from an answer Ron de Bruin gave Pam eralier today. I used: Worksheets(1).Range(Cells(1, 1), Cells(52, curCol)).PrintOut (Excuse wordwrapping) I want to thank everyone who helps answer questions on this forum! I learn a lot from reading answers you all give to other people as well as from my own posted questions! Thanks Again! Kevin -----Original Message----- I have a spreadsheet that has a fixed number of rows, but a variable number of columns that may contain data. I am trying to set the print area depending on how many columns have data. I have a looping structure in my code which moves through the columns and locates the last column containing data. This loop works correctly, but it returns a number. The printarea wants the R1C1 cell notation. I could preset my print area but more than likely in 90% of the cases, only half of the available columns or less will be used. I don't want to print the whole spreadsheet unless I need to so the printouts will be easy to read. I have tried using the Activecell.Address property and other methods to no avail. I am sure it is just a syntax problem, but I am not finding any good examples in the Helpless Help! Help! Thanks in advance. Kevin . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting The Print-Area ? | New Users to Excel | |||
Setting print area | Excel Discussion (Misc queries) | |||
How to Dynamically Set Print Area | Excel Discussion (Misc queries) | |||
setting print area via VBA | Excel Programming | |||
Excel 2000 Dynamically Set Print Area | Excel Programming |