How to get the print area with excel vba? 2

[smart89]

the print area displayed A1:I216 and J1:S216 after print preview,I want to get the area with VBA code ,how can i get it? thanks

 

[rmikesmith]

From the Help File:

Worksheets("Sheet1").PageSetup.PrintArea

Regards,
Mike

 

[smart89]

thanks. but it couldn't work.the code "Worksheets("Sheet1").PageSetup.PrintArea" returned empty string ("") only.

 

[dgillz]

This just means you have no defined print area setup. I just tested the following code and it works:

Private Sub CommandButton1_Click()
Dim don As String
don = Worksheets("Sheet1").PageSetup.PrintArea
MsgBox don
End Sub


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[Gavona]

To add to dgillz's response. If the print area is not set then excel prints the used range. Worksheets("Sheet1").UsedRange

Gavin

 

[smart89]

the print area was not set ,but it displayed 8 pages when print preview.How to get the area without setting print area?

in addition, another question for help.How to print the current page containing active cells? thanks again.

 

[combo]

HPageBreak(s) and VPageBreak(s) can be used to get/set pagination. They also force excel to define print area.
The PrintOut method prints specified area. It can be applied to workbook, sheet or range. Seems that the last case could be useful in your case.

combo

 

[rmikesmith]

the print area was not set ,but it displayed 8 pages when print preview.How to get the area without setting print area?

Using a combination of the suggestions so far:

Dim sPrintAreaAddress As String

   sPrintAreaAddress = Worksheets("Sheet1").PageSetup.PrintArea
   If sPrintAreaAddress = "" Then
     sPrintAreaAddress = Worksheets("Sheet1").UsedRange.Address
   End if

Regards,
Mike

 

[smart89]

thank combo and rmikesmith.but the used range was different to print area.the used range was "B3:B133" and the print area was "A1:I162" in my case.How can i get the range "A1:I162"?

 

[dgillz]

Did my code not work? It works perfectly for me.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[smart89]

no,it didn't work. did you define the print area before running? I didn't want to define print area setup manually.The print area was defined by excel automatically.thanks again for reply.

 

[rmikesmith]

smart89,

You are correct in that dgillz code will only work if a print area is already set; otherwise, an empty string is returned.

I don't know of any reasonable way to find the internal print area Excel uses if one is not explicitly set (i.e., what you see in Print Preview mode). If your data starts in, say, column M and you do a print preview, you will find that Excel includes all the blank columns before this in its print area (and, as you indicated, the UsedRange will be different, beginning in column M, not column A). The real problem is that Excel will also adjust its internal print area if text in a cell runs into adjacent cells that are beyond the width of a page, adding another page automatically and effectively breaking the text over pages. I don't know how you can account for that easily using code. It can probably be accomplished, but is it worth it? [Chip Pearson, Microsoft MVP, has some code on his website that gets into the nitty-gritty of screen and cell metrics to position a Userform at a particular cell. You would probably need similar calculations, including font size metrics to determine how many columns wide the internal print area is. Not a trivial task]

Regards,
Mike

 

[smart89]

rmikesmith,

Thanks for reply. Printing the current page containing active cells is the origina purpose of mine. I could do this if I know the range of print area and the active cells.This is useful when I modify some cells after printing out all pages.For nothing remains but to print the modified page only.This is just like Microsoft word .

On the side,the code like this in word.
ActiveDocument.PrintOut Range:=wdPrintCurrentPage

 

[rmikesmith]

I see what you're trying to do. The trick is tying a particular range (cell) to a print page. Again, I don't believe that it's a simple proposition. I'll give this some thought.


Regards,
Mike

 

[combo]

Coming back to my post: if you need to print only the area around active cell, why not define the range you want to print and just print it:
Worksheets("Sheet1").Range(AddressToDefine).PrintOut

Otherwise, to follow excel's pagination, I would loop in the surrounding area and test for page breaks. Having them, I would define the range to print.

combo