Last cell with data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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 said:
Without looping through all the cells in a column, how can I determine the
Click to expand...
last cell that has data in it for a given column?
 
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
Click to expand...
can I determine the last cell that has data in it for a
given column?
 
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
Click to expand...
can I determine the last cell that has data in it for a
given column?
 
..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
 
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.
 
Back
Top