Last cell with data
 

Without looping through all the cells in a column, how can I determine the last cell that has data in it for a given column?

Last cell with data
 

try
Dim i as integer

i= Cells(iRowNo,iColumnNo).End(xlDown).Row

and You'll get the number of last row, in the given column, containing data.
Or You may try
i=UsedRange.Rows.Count
and You'll get the number of last row containing any data in the whole
worksheet

"COM" wrote in message
...
Without looping through all the cells in a column, how can I determine the
last cell that has data in it for a given column?

Last cell with data
 

One way, if you need to use it a lot, add the following
function to a module (the row that starts "lngRow ="
should not wrap):

Function RowLastInColumn(argColumn As Integer)

Dim lngRow As Long
On Error Resume Next
lngRow = ActiveSheet.Columns(argColumn).Find(What:="*",
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If IsEmpty(lngRow) Then RowLastInSheet = 0
If Not IsEmpty(lngRow) Then RowLastInColumn = lngRow

End Function

Then call the function and supply the desired column
number like this:

Sub Get_Last_Row_In_Column()

MsgBox RowLastInColumn(2)

End Sub

IF the column is unused, it returns zero.

There are many methods. Hope this helps.


-----Original Message-----
Without looping through all the cells in a column, how
can I determine the last cell that has data in it for a
given column?
.

Last cell with data
 

Try this:

Sub LastRow()
' Grab the current worksheet.
Dim shtCurrent As Worksheet
Set shtCurrent = ActiveSheet
' Grab the last cell in column 1 (A)
Dim rngLastCell As Range
Set rngLastCell = shtCurrent.UsedRange.Columns(1).Rows
(shtCurrent.UsedRange.Rows.Count)
' Select it.
rngLastCell.Select
End Sub

HTH
-----Original Message-----
Without looping through all the cells in a column, how
can I determine the last cell that has data in it for a
given column?
.

Last cell with data
 

..End(xlDown) is the same as selecting a cell, holding down the End key and
pressing the down key.
It will move down to the end of the current column's region, not the last
row containing data in that column.
It has it's uses when you have lists stacked on top of eachother.

If it's one list for the whole column, read on.

The following gets the first row up from the very last row in column
iColumnNo that contains data
Same as clicking cell A65536, holding down the End key and pressing the up
key.
i = Cells(Rows.Count, iColumnNo).End(xlUp).Row


"Tomek" wrote in message
...
try
Dim i as integer

i= Cells(iRowNo,iColumnNo).End(xlDown).Row

and You'll get the number of last row, in the given column, containing
data.
Or You may try
i=UsedRange.Rows.Count
and You'll get the number of last row containing any data in the whole
worksheet

"COM" wrote in message
...
Without looping through all the cells in a column, how can I determine
the
last cell that has data in it for a given column?

Last cell with data
 

Yes, that's right. I wasn't perhaps precise enough.. It's also the same as
selecting a cell in a given column and pressing Ctrl+Down key combination.
Assuming that the given column contains data in all cells this method is
correct and gives expected result.

"Rob van Gelder" wrote in message
...
.End(xlDown) is the same as selecting a cell, holding down the End key and
pressing the down key.
It will move down to the end of the current column's region, not the last
row containing data in that column.
It has it's uses when you have lists stacked on top of eachother.

If it's one list for the whole column, read on.

The following gets the first row up from the very last row in column
iColumnNo that contains data
Same as clicking cell A65536, holding down the End key and pressing the up
key.
i = Cells(Rows.Count, iColumnNo).End(xlUp).Row