Last used cell in a column

[Guest]

I have used the following for a while:

Function lcell(col As Integer) As Long
Dim lastcell As Range
Set lastcell = Cells(Rows.Count, col).End(xlUp)
lcell = lastcell.Row
End Function

=lcell(1) is supposed to return the row number of the last used cell in
column A. Today I discovered the code ignores row 65536; so if A65536 is
occupied, another cell in column A is being returned.

Why?

 

[Jim Rech]

The End method works exactly as the End key does in selecting cells, so as
you're a long time Excel user, I'm sure you know what's going on. So
modifiy your function to check whether Cells(Rows.Count, col) has an entry,
in which case it should return Rows.Count, or else continue as you have it.

--
Jim
|I have used the following for a while:
|
| Function lcell(col As Integer) As Long
| Dim lastcell As Range
| Set lastcell = Cells(Rows.Count, col).End(xlUp)
| lcell = lastcell.Row
| End Function
|
| =lcell(1) is supposed to return the row number of the last used cell in
| column A. Today I discovered the code ignores row 65536; so if A65536 is
| occupied, another cell in column A is being returned.
|
| Why?
| --
| Gary''s Student

 

[Guest]

Hi,

Because End looks for the end of contiguous ranges. If A65536 is populated
the End(xlUp) will find the first full cell beneath a 'gap' of empty cells in
column A.

I don't know what you are trying to do but I recently found 'SpecialCells'
refered to in another post - which may apply better to the kind of end row
issue you are looking at - check it out in 'help'.

Regards,

Chris.