Patricia Shanahan said:
As it happens, this function was written to be called from one of my
own
macros, which always passes the top left and bottom right corners of a
table it has just constructed. However, I prefer to make my code
robust
and reusable, so I'll try to remember to deal with other pairs of
opposite corners in future.
I'm not sure how your suggested change would work in practice.
Suppose, for example, cornerBase were the bottom left corner, instead
of
top left. I would need row to go negative to get to rows above
cornerBase. Perhaps the code would need more reworking to deal with
the
general case?
You're right, of course. Your nested loops do need to operate from the
top left corner.
There are a variety of ways to deal with this issue, and while I do have
programming background I've only recently had occasion to work with VBA
and Excel -- so I'm all the time discovering some new way to use native
properties and methods; or even discovering properties or methods I
didn't even realize were there.
Some untested air code for taking a pair of opposite corners and
determining upper left and lower right:
Option Explicit
Function GetUpperLeft(ByRef Corner1 As Range, _
ByRef Corner2 As Range, _
ByRef UpperLeft As Range, _
ByRef LowerRight As Range) As Long
' Returns the count of cells in the region defined by
' the pair of opposite corners Corner1 and Corner2.
' Exits with UpperLeft and LowerRight pointing to those
' respective cells.
' Error checking could be added to test for a variety of
' conditions, such as Corner1 and Corner2 not belonging
' to the same worksheet, or either or both objects not
' being a single cell. As written, no error checking is
' performed, and the upper left cell of each is taken as
' the reference, if either object is a range of more than
' one cell.
' If desired, negative return values could be defined to
' report specific error conditions; ie:
' -1 = Corner1 contains multiple cells
' -2 = Corner2 contains multiple cells
' -3 = Both contain multiple cells
' etc.
Dim cellCount As Long ' Number of cells in specified region
Dim Temp As Range ' Temporary Range object
Set Temp = Range(Corner1.Cells(1), Corner2.Cells(1))
cellCount = Temp.Count
Set UpperLeft = Temp.Cells(1)
Set LowerRight = Temp.Cells(cellCount)
GetUpperLeft = cellCount
Set Temp = Nothing
End Function