MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How do I obtain the 4 corners of the worksheet being displayed

  Asked By: Phil    Date: Feb 16    Category: MS Office    Views: 748

At any given moment, I may work on a cell, say F29 and can view only a
subset of the entire worksheet. This subset is displayed in the window
and has four corners, say C21, N21, C44, N44. In VBA, how do I obtain
these four values?



3 Answers Found

Answer #1    Answered By: Sadie Black     Answered On: Feb 16

Sub TryThis()
Dim strN As String, strFirstCell As String, strLastCell As String
Dim strTopRightCell As String, strBottomLeftCell As String

strN = ActiveWindow.VisibleRange.Address
strFirstCell = Range(strN).Cells(1).Address
strLastCell = Range(strN).Cells(Range(strN).Cells.Count).Address
strTopRightCell = Cells(Range(strFirstCell).Row, Range _
strBottomLeftCell = Cells(Range(strLastCell).Row, Range _

End Sub

Answer #2    Answered By: Viveka Fischer     Answered On: Feb 16

Excellent! What more can I say except Thank you!

Answer #3    Answered By: Cadeo Nguyen     Answered On: Feb 16

I've done some vaguely similar work  with this in the past. Looking through
some code, I get the following:

Row at top seems to be: ActiveWindow.ScrollRow
Column at left seems to be: ActiveWindow.ScrollColumn

Unfortunately, what I was doing was converting everything to pixels so I
could position popup items, so I never pursued finding the right column or
the bottom row, just the pixel extents of the window.

However, I would have used Google to find a lot of what I wanted. If you go
looking for ActiveWindow.ScrollRow and .ScrollColumn with other keywords
like "right" and "bottom", I suspect you'll find what you want.

Also, have a look in help for other attributes to ActiveWindow similar to
the two I've mentioned.