Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last used cell in a column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last used cell in a column
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 "Gary''s Student" wrote in message ... |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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last used cell in a column
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. -- Chris Marlow MCSD.NET, Microsoft Office XP Master "Gary''s Student" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maddening Dilemma - Compare each cell within column a to each cell in column b and find unique matches | Excel Worksheet Functions | |||
Sum Cell Values of one column based on Another Cell Value in a different column | Excel Worksheet Functions | |||
compare cells in column to criteria, then average next column cell | Excel Worksheet Functions | |||
Loop through column headers to search from column name and get cell range | Excel Programming | |||
Format cell in column B based on value in the next cell (column c) | Excel Discussion (Misc queries) |