Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
I see so many different ways to get the last row/ last column/ last cell ona
sheet. Since I need to use these functions so often, I am wondering what is truly and definitively the best (most accurate, most efficient & quickest, in that order) way to determine last row and/or last column, and/or last cell. there are several ways on this thread alone: http://www.microsoft.com/office/comm...=en-us&m=1&p=1 What is the best way, and why? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
For a specific column or row, I always use code like
Dim LastRow As Long Dim LastCol As Long LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ' Column A LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column ' Row 1 Debug.Print LastRow, LastCol It works fine and I use it all the time in my commercial applications. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "justme" wrote in message ... I see so many different ways to get the last row/ last column/ last cell ona sheet. Since I need to use these functions so often, I am wondering what is truly and definitively the best (most accurate, most efficient & quickest, in that order) way to determine last row and/or last column, and/or last cell. there are several ways on this thread alone: http://www.microsoft.com/office/comm...=en-us&m=1&p=1 What is the best way, and why? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
Chip, This code is so short and simple. If it works for everything, then why would people go through writing all sorts of functions to be called, etc. to figure out the same thing? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
Chip's code will only work if all cells in A have data
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ' Column A See my site for another way http://www.rondebruin.nl/copy1.htm Text from my site: You can also check one row or column to find the last cell with a value. Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row This will give you the last row with data in Column A + 1 Row Lc = Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset (0, 1).Column This will give you the last column with data in Row 1 + 1 column Be aware that if you copy a range with also empty cells in it, It is possible that the next time you copy to Sheets("Sheet2")some lines will be overwritten. Use the Functions to avoid this kind of problems. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "justme" wrote in message ... Chip, This code is so short and simple. If it works for everything, then why would people go through writing all sorts of functions to be called, etc. to figure out the same thing? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
Chip's code will only work if all cells in A have data
I think you mean that if at least one cell in A has data, which I took as an assumption. It will fail if A(Rows.Count) has data in it. But I NEVER let data go down that far. It in no way requires that ALL the cells in A have data. It will return the last row even if A has blanks interspersed within the data. Unless A is completely empty or A(Rows.Count) has data, it works fine. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ron de Bruin" wrote in message ... Chip's code will only work if all cells in A have data LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ' Column A See my site for another way http://www.rondebruin.nl/copy1.htm Text from my site: You can also check one row or column to find the last cell with a value. Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row This will give you the last row with data in Column A + 1 Row Lc = Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset (0, 1).Column This will give you the last column with data in Row 1 + 1 column Be aware that if you copy a range with also empty cells in it, It is possible that the next time you copy to Sheets("Sheet2")some lines will be overwritten. Use the Functions to avoid this kind of problems. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "justme" wrote in message ... Chip, This code is so short and simple. If it works for everything, then why would people go through writing all sorts of functions to be called, etc. to figure out the same thing? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
Ron,
I am not sure why you are falsely disparaging Chip's code, then posting functionally identical code??? Your motivation escapes me??? Even if he had suggested xlDown, it seems you could have been less insulting in your response particularly to someone who was posting excellent solutions back when you were a newby asking for help. Chip: LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ' Column A Ron: Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row Chip: LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column ' Row 1 Ron: Lc = Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset (0, 1).Column As I said, functionally, I don't see the difference (besides you add 1 to the result which you explained). -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Chip's code will only work if all cells in A have data LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ' Column A See my site for another way http://www.rondebruin.nl/copy1.htm Text from my site: You can also check one row or column to find the last cell with a value. Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row This will give you the last row with data in Column A + 1 Row Lc = Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset (0, 1).Column This will give you the last column with data in Row 1 + 1 column Be aware that if you copy a range with also empty cells in it, It is possible that the next time you copy to Sheets("Sheet2")some lines will be overwritten. Use the Functions to avoid this kind of problems. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "justme" wrote in message ... Chip, This code is so short and simple. If it works for everything, then why would people go through writing all sorts of functions to be called, etc. to figure out the same thing? Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
Hi Chip/Tom
If you only want to know it for one column there is no problem There are a few postings about last cells today so maybe I am confused <g But if you copy a range to the last row + 1 in A and the range have empty cell in A the next time It will overwrite data. That's why I say use the function See my site for another way http://www.rondebruin.nl/copy1.htm Be aware that if you copy a range with also empty cells in it, It is possible that the next time you copy to Sheets("Sheet2")some lines will be overwritten. Sorry, I have to much xml in my head . <vbg -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chip Pearson" wrote in message ... Chip's code will only work if all cells in A have data I think you mean that if at least one cell in A has data, which I took as an assumption. It will fail if A(Rows.Count) has data in it. But I NEVER let data go down that far. It in no way requires that ALL the cells in A have data. It will return the last row even if A has blanks interspersed within the data. Unless A is completely empty or A(Rows.Count) has data, it works fine. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ron de Bruin" wrote in message ... Chip's code will only work if all cells in A have data LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ' Column A See my site for another way http://www.rondebruin.nl/copy1.htm Text from my site: You can also check one row or column to find the last cell with a value. Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row This will give you the last row with data in Column A + 1 Row Lc = Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset (0, 1).Column This will give you the last column with data in Row 1 + 1 column Be aware that if you copy a range with also empty cells in it, It is possible that the next time you copy to Sheets("Sheet2")some lines will be overwritten. Use the Functions to avoid this kind of problems. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "justme" wrote in message ... Chip, This code is so short and simple. If it works for everything, then why would people go through writing all sorts of functions to be called, etc. to figure out the same thing? Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
Ron,
Ron If you only want to know it for one column there is no problem and that is what Chip Said: Chip For a specific column or row, I always use code like Also, the OP's question and Chip's answer had nothing to do with copying. You added that scenario and then used it to "discredit" Chip's answer or at least point it out as flawed as I read it??? Just as an added consideration, the lastrow function you have found in this newgroup and documented on your site has limitations as well. If any scenario is to be fantasized, then if column M (as an example) contained unrelated data farther down the sheet, the function would return the incorrect value for the last row in column A. Also, Find does not find a cell containing the Null string "" entered when you do edit=copy and then edit paste special, values for a cell containing the formula like =If(true,"",""), which may or may not be desirable (end(xlup) does). So while a robust solution, certainly not universal or for use without understanding (undocumented on your site). -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Hi Chip/Tom If you only want to know it for one column there is no problem There are a few postings about last cells today so maybe I am confused <g But if you copy a range to the last row + 1 in A and the range have empty cell in A the next time It will overwrite data. That's why I say use the function See my site for another way http://www.rondebruin.nl/copy1.htm Be aware that if you copy a range with also empty cells in it, It is possible that the next time you copy to Sheets("Sheet2")some lines will be overwritten. Sorry, I have to much xml in my head . <vbg -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chip Pearson" wrote in message ... Chip's code will only work if all cells in A have data I think you mean that if at least one cell in A has data, which I took as an assumption. It will fail if A(Rows.Count) has data in it. But I NEVER let data go down that far. It in no way requires that ALL the cells in A have data. It will return the last row even if A has blanks interspersed within the data. Unless A is completely empty or A(Rows.Count) has data, it works fine. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ron de Bruin" wrote in message ... Chip's code will only work if all cells in A have data LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ' Column A See my site for another way http://www.rondebruin.nl/copy1.htm Text from my site: You can also check one row or column to find the last cell with a value. Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row This will give you the last row with data in Column A + 1 Row Lc = Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset (0, 1).Column This will give you the last column with data in Row 1 + 1 column Be aware that if you copy a range with also empty cells in it, It is possible that the next time you copy to Sheets("Sheet2")some lines will be overwritten. Use the Functions to avoid this kind of problems. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "justme" wrote in message ... Chip, This code is so short and simple. If it works for everything, then why would people go through writing all sorts of functions to be called, etc. to figure out the same thing? Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
Thanks for the info Tom
While working on other things I better not answering newsgroup postings the next time. Sorry -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tom Ogilvy" wrote in message ... Ron, Ron If you only want to know it for one column there is no problem and that is what Chip Said: Chip For a specific column or row, I always use code like Also, the OP's question and Chip's answer had nothing to do with copying. You added that scenario and then used it to "discredit" Chip's answer or at least point it out as flawed as I read it??? Just as an added consideration, the lastrow function you have found in this newgroup and documented on your site has limitations as well. If any scenario is to be fantasized, then if column M (as an example) contained unrelated data farther down the sheet, the function would return the incorrect value for the last row in column A. Also, Find does not find a cell containing the Null string "" entered when you do edit=copy and then edit paste special, values for a cell containing the formula like =If(true,"",""), which may or may not be desirable (end(xlup) does). So while a robust solution, certainly not universal or for use without understanding (undocumented on your site). -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Hi Chip/Tom If you only want to know it for one column there is no problem There are a few postings about last cells today so maybe I am confused <g But if you copy a range to the last row + 1 in A and the range have empty cell in A the next time It will overwrite data. That's why I say use the function See my site for another way http://www.rondebruin.nl/copy1.htm Be aware that if you copy a range with also empty cells in it, It is possible that the next time you copy to Sheets("Sheet2")some lines will be overwritten. Sorry, I have to much xml in my head . <vbg -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chip Pearson" wrote in message ... Chip's code will only work if all cells in A have data I think you mean that if at least one cell in A has data, which I took as an assumption. It will fail if A(Rows.Count) has data in it. But I NEVER let data go down that far. It in no way requires that ALL the cells in A have data. It will return the last row even if A has blanks interspersed within the data. Unless A is completely empty or A(Rows.Count) has data, it works fine. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ron de Bruin" wrote in message ... Chip's code will only work if all cells in A have data LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ' Column A See my site for another way http://www.rondebruin.nl/copy1.htm Text from my site: You can also check one row or column to find the last cell with a value. Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row This will give you the last row with data in Column A + 1 Row Lc = Sheets("Sheet2").Range("IV1").End(xlToLeft).Offset (0, 1).Column This will give you the last column with data in Row 1 + 1 column Be aware that if you copy a range with also empty cells in it, It is possible that the next time you copy to Sheets("Sheet2")some lines will be overwritten. Use the Functions to avoid this kind of problems. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "justme" wrote in message ... Chip, This code is so short and simple. If it works for everything, then why would people go through writing all sorts of functions to be called, etc. to figure out the same thing? Thanks |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
Hi everybody,
Thank you all for your responses. I was actually looking for a formula that would always find the last cell in a sheet no matter which column was the longest, i.e. last row, last column. Is there such a thing? thank you. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
Hi justme
Harlan posted this two functions You can use them in a worksheet cell like this =lc() and =lr() Function lr(Optional r As Range) As Variant Dim ur As Range, c As Range, i As Long, n As Long 'there arguments for & against volatility - optional to uncomment Application.Volatile If r Is Nothing Then Set r = Application.Caller If Not TypeOf r Is Range Then Set r = ActiveCell Set ur = r.Parent.UsedRange n = ur.Rows.Count For i = n To 1 Step -1 Set c = ur.Cells(i, 1) If Not IsEmpty(c.Value) Then Exit For If Not IsEmpty(c.End(xlToRight).Value) Then Exit For Next i lr = ur.Row + i - 1 End Function Function lc(Optional r As Range) As Variant Dim ur As Range, c As Range, i As Long, n As Long 'there arguments for & against volatility - optional to uncomment Application.Volatile If r Is Nothing Then Set r = Application.Caller If Not TypeOf r Is Range Then Set r = ActiveCell Set ur = r.Parent.UsedRange n = ur.Columns.Count For i = n To 1 Step -1 Set c = ur.Cells(1, i) If Not IsEmpty(c.Value) Then Exit For If Not IsEmpty(c.End(xlDown).Value) Then Exit For Next i lc = ur.Column + i - 1 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "justme" wrote in message ... Hi everybody, Thank you all for your responses. I was actually looking for a formula that would always find the last cell in a sheet no matter which column was the longest, i.e. last row, last column. Is there such a thing? thank you. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
A formula or code.
for code: Ron gave you a link to the lastrow and lastcol functions which you have already used per a previous thread. Any formula should be based on the data being used. Otherwise you waste resources checking things that don't need to be checked. -- Regards, Tom Ogilvy "justme" wrote in message ... Hi everybody, Thank you all for your responses. I was actually looking for a formula that would always find the last cell in a sheet no matter which column was the longest, i.e. last row, last column. Is there such a thing? thank you. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
Hello All,
Actually, I did mean code. I have not had much sleep lately. Here is what I am getting at, Tom: I have seen different ways to find the last row or last column or last cell, using find(What) or xllastcell or usedRange or rows.count. Many of them rely on a certain row or column to be named, or some other condition. Some of them require calling functions and some don't. Some go down to row 65536 and back up again. Ron's post above is yet another. But I would just like to know which is the most reliable to use on any worksheet, sight unseen, whether you're not sure which column is the longest, if rows are missing, if a cell here or there is gone or if there are large gaps in data areas, what would be the most reliable, most succinct code (and hopefully doesn't take too long). I just want to find the best, all-inclusive way so I don't have to take everything into account about any particular worksheet when I want to find a "last". So I am looking for expert opinions. I think the following sub posted by you, Tom may be the answer, but I don't know if there are things it may not take into account, and that's why I started this thread. Dim RealLastRow As Long Dim RealLastColumn As Long Range("A1").Select 'On Error Resume Next RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row RealLastColumn = _ Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column Cells(RealLastRow, RealLastColumn).Select ' find last row I really appreciate all the posts here and the others I have found. I copy many of them to my notebook. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
for that scenario, use the lastrow and lastcol functions at Ron's site:
http://www.rondebruin.nl/copy1.htm "justme" wrote in message ... Hello All, Actually, I did mean code. I have not had much sleep lately. Here is what I am getting at, Tom: I have seen different ways to find the last row or last column or last cell, using find(What) or xllastcell or usedRange or rows.count. Many of them rely on a certain row or column to be named, or some other condition. Some of them require calling functions and some don't. Some go down to row 65536 and back up again. Ron's post above is yet another. But I would just like to know which is the most reliable to use on any worksheet, sight unseen, whether you're not sure which column is the longest, if rows are missing, if a cell here or there is gone or if there are large gaps in data areas, what would be the most reliable, most succinct code (and hopefully doesn't take too long). I just want to find the best, all-inclusive way so I don't have to take everything into account about any particular worksheet when I want to find a "last". So I am looking for expert opinions. I think the following sub posted by you, Tom may be the answer, but I don't know if there are things it may not take into account, and that's why I started this thread. Dim RealLastRow As Long Dim RealLastColumn As Long Range("A1").Select 'On Error Resume Next RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row RealLastColumn = _ Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column Cells(RealLastRow, RealLastColumn).Select ' find last row I really appreciate all the posts here and the others I have found. I copy many of them to my notebook. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
For that scenario, use Ron's implementation of lastrow and last column
functions at http://www.rondebruin.nl/copy1.htm It uses the same approach as what you attributed to me but breaks it into two parts. My code (not originated by me), uses the separate last row and column locations to identify the cell at their intersection - so it gives the lastcell from a rectangle perspective. note: Don't be mislead by Cells(rows.count,column).End(xlup) this operates almost instantaneously. -- Regards, Tom Ogilvy "justme" wrote in message ... Hello All, Actually, I did mean code. I have not had much sleep lately. Here is what I am getting at, Tom: I have seen different ways to find the last row or last column or last cell, using find(What) or xllastcell or usedRange or rows.count. Many of them rely on a certain row or column to be named, or some other condition. Some of them require calling functions and some don't. Some go down to row 65536 and back up again. Ron's post above is yet another. But I would just like to know which is the most reliable to use on any worksheet, sight unseen, whether you're not sure which column is the longest, if rows are missing, if a cell here or there is gone or if there are large gaps in data areas, what would be the most reliable, most succinct code (and hopefully doesn't take too long). I just want to find the best, all-inclusive way so I don't have to take everything into account about any particular worksheet when I want to find a "last". So I am looking for expert opinions. I think the following sub posted by you, Tom may be the answer, but I don't know if there are things it may not take into account, and that's why I started this thread. Dim RealLastRow As Long Dim RealLastColumn As Long Range("A1").Select 'On Error Resume Next RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row RealLastColumn = _ Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column Cells(RealLastRow, RealLastColumn).Select ' find last row I really appreciate all the posts here and the others I have found. I copy many of them to my notebook. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
The best sub for last row / last column / last cell?
Thank you, everyone, for your time. This is great.
:) :) :) |
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 | |||
Hiding column if cell in previous column is empty-revised | Excel Programming | |||
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) |