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?

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" <(E-Mail Removed)> wrote in message
news:08C7A375-E697-4C5E-BA49-(E-Mail Removed)...
>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?

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

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" <(E-Mail Removed)> wrote in message news:6A7AEC0F-394D-47CB-A188-(E-Mail Removed)...
>
> 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

> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:6A7AEC0F-394D-47CB-A188-(E-Mail Removed)...
>>
>> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:6A7AEC0F-394D-47CB-A188-(E-Mail Removed)...
>>
>> 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

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" <(E-Mail Removed)> wrote in message news:O$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:6A7AEC0F-394D-47CB-A188-(E-Mail Removed)...
>>>
>>> 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
>
>

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" <(E-Mail Removed)> wrote in message
news:eu%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:O$(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:6A7AEC0F-394D-47CB-A188-(E-Mail Removed)...
>>>>
>>>> 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
>>

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" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:eu%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:O$(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:6A7AEC0F-394D-47CB-A188-(E-Mail Removed)...
>>>>>
>>>>> 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
>>>
>

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.