Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i find specific data in a cell and its colocated cell data | Excel Worksheet Functions | |||
converting numerical data in one cell to word data in another cell | Excel Worksheet Functions | |||
how to find number in a cell , a cell contains character data ornumeric data | Excel Worksheet Functions | |||
moving data to a cell, not pulling data to the cell | Excel Worksheet Functions | |||
Find MAX data in sheet (Cell) - Any cell that exceeds X amount of data | Excel Discussion (Misc queries) |