Circular Reference?
 

I created (created may be a strong word since they really aren't
original) the two small functions shown below so that in my Excel
macros I could determine how far out in a spreadsheet that data
actually can be found. Anyway, open up a new workbook and paste this
into a module. Then go to Sheet1 (which should be blank) and go to any
random cell. Type in =LastCol() and hit enter and notice that the
value displayed in the cell is in fact the index corresponding to the
column number of the farthest right value. What's interesting is that
it's also the index corresponding to the column number of the formula.
Isn't this a circular reference? You cant go into cell A1 and say
=A1*2. So why does this return a value which wouldn't exist without
the formula itself. What I mean is that the answer to the question is
driven by the question itself, or at the very least the location of the
question. Anyway, it doesn't hurt me because I'm using these functions
in code rather than in worksheets (i.e. x = lastrow() and y =
lastcol()). Just a peculiarity that I found interesting.

Function LastCol() As Integer
' LastCol() Returns An Integer Value Corresponding To The Index Of
The Farthest Right Non-Empty Column In A Worksheet
On Error Resume Next
LastCol = Cells.Find("*", [A1], xlValues, , xlByColumns,
xlPrevious).Column
End Function

Function LastRow() As Integer
' LastRow() Returns An Integer Value Corresponding To The Index Of
The Farthest Down Non-Empty Row In A Worksheet
On Error Resume Next
LastRow = Cells.Find("*", [A1], xlValues, , xlByRows,
xlPrevious).Row
End Function

Circular Reference?
 

It is not circular because =LastCol() doesn't care what is in the cell, just
that it is not blank.

thanks for the =LastCol() formula, I can actually put that to use, assuming
that a =lastrow() exists also.



"ags5406" wrote:

I created (created may be a strong word since they really aren't
original) the two small functions shown below so that in my Excel
macros I could determine how far out in a spreadsheet that data
actually can be found. Anyway, open up a new workbook and paste this
into a module. Then go to Sheet1 (which should be blank) and go to any
random cell. Type in =LastCol() and hit enter and notice that the
value displayed in the cell is in fact the index corresponding to the
column number of the farthest right value. What's interesting is that
it's also the index corresponding to the column number of the formula.
Isn't this a circular reference? You cant go into cell A1 and say
=A1*2. So why does this return a value which wouldn't exist without
the formula itself. What I mean is that the answer to the question is
driven by the question itself, or at the very least the location of the
question. Anyway, it doesn't hurt me because I'm using these functions
in code rather than in worksheets (i.e. x = lastrow() and y =
lastcol()). Just a peculiarity that I found interesting.

Function LastCol() As Integer
' LastCol() Returns An Integer Value Corresponding To The Index Of
The Farthest Right Non-Empty Column In A Worksheet
On Error Resume Next
LastCol = Cells.Find("*", [A1], xlValues, , xlByColumns,
xlPrevious).Column
End Function

Function LastRow() As Integer
' LastRow() Returns An Integer Value Corresponding To The Index Of
The Farthest Down Non-Empty Row In A Worksheet
On Error Resume Next
LastRow = Cells.Find("*", [A1], xlValues, , xlByRows,
xlPrevious).Row
End Function