How to find Used Range and Print Area?

[Joseph Geretz]

Originally, I was using the following statement to copy an image of a
worksheet, so that the worksheet information can be displayed as read-only
to the user:

SourceWorksheet.UsedRange.CopyPicture

(The reason we use this esoteric approach is because we're not in control of
the source document. We don't know what protection may already be applied.
We don't know what information or widgets are contained within the
worksheet. We're just given a worksheet of unknown origin and we need to
display it as read-only to the user.)

We found however, that this doesn't result in a faithful image of the
underlying worksheet: The following is much more effective:

SourceWorksheet.UsedRange.CopyPicture XlPictureAppearance.xlScreen,
XlCopyPictureFormat.xlBitmap

However, the xlBitmap format must be much more intensive than the xlPicture
format. I'm finding that with huge worksheets (i.e the worksheet developer
has placed information, somethimes just background coloring, down to a
ridiculous right/bottom corner) this take a looooong tim to accomplish.
However, in all of these cases, the user has set a more reasonable print
area. So here's what I'd like to do.

1. Figure our what range is actually represented by UsedRange
2. If this exceeds a defined threshold, figure out the range represented by
PrintArea.
3. If this still exceeds the defined threshold, then I'll just use the free
Excel Viewer to display the document.

If you can tell me how to convert these two 'logical' ranges, UsedRange and
PrintArea to defined cell coordinates, I'll be able to give this a shot.

Thanks for your help!

- Joseph Geretz -

 

[Tom Ogilvy]

Sub cc()
Dim s As String
Dim rng As Range, prng As Range
Set rng = ActiveSheet.UsedRange
s = rng.Address
On Error Resume Next
Set prng = ActiveSheet.Names("Print_Area").RefersToRange
s = rng.Address & " - " & prng.Address
On Error GoTo 0
MsgBox s
End Sub

When you say Print_Area. Has the user actually set a printarea or do you
mean Excel prints something less than what the usedrange reports when the
print_area has not been set? (I would find that unusual).

 

[Joseph Geretz]

Thanks Tom,

I'm grabbing both the UsedRange and the PrintRange and then using the
..Height and .Width I can see immediately that the PrintRange is much smaller
than the UsedRange. Using the smaller PrintRange I am able to accomplish the
CopyPicture in a very short period of time (sub-second).

I am curious about what the .Height and .Width property correspond to.
Pixels? These don't seem to relate to rows and columns.

Another thing I am curious about is, how would I go about getting the
coordinates of the top left cell and bottom right cell of a range, if I were
so inclined?

Thanks for your help!

- Joe Geretz -