Data in a list

fascsam

New Member
Joined
Jul 22, 2002
Messages
30
Is there a way I can use a formula that will simply return the LAST item in a column's list?

Help!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
The following code will work as long as you don't have any blanks in your column:

MyLastItem = Range("A1").End(xlDown).Value

In place of "Range("A1")", enter in any cell of the column you wish to check.
 
Upvote 0
By the way, I failed to mention that this VBA code that you would need to put in a module (macro).

If you want to put the value in a certain cell instead of storing it in a variable, use the format listed below.

Say you wanted to return the value in cell E10. The code would be:

Range("E10") = Range("A1").End(xlDown).Value
 
Upvote 0
Hi fascsam:

Welcome to the Board!

You can use the MATCH and INDEX functions to get the last item in a list. See the worksheet simulation ...
Book2
HIJK
1
25
33
4rakehose
5broom
69
7shovel
8
913
10trimmer
1122
12132
13hose
Sheet3
</SPAN>

Perhps others can suggest a shorter formula -- but this one does work.

Regards!

Yogi
 
Upvote 0
Hi jmiskey:

How about if we modify your code a little so that it can pick up the last item in column A even if there are blanks ...

Range("E10") = Range("A65536").End(xlUp).Value

What do you think?

Regards!

Yogi
 
Upvote 0
On 2002-10-11 15:51, Yogi Anand wrote:
Hi fascsam:

Welcome to the Board!

You can use the MATCH and INDEX functions to get the last item in a list. See the worksheet simulation ...
Book2
HIJK
1
25
33
4rakehose
5broom
69
7shovel
8
913
10trimmer
1122
12132
13hose
Sheet3
</SPAN>

Perhps others can suggest a shorter formula -- but this one does work.

Regards!

Yogi
 
Upvote 0
Yogi - I like your formula.. I don't know anything about VB.. but I can't seem to get yours to work in my spreadsheet..

What is the 9.99999999999 etc. for?
 
Upvote 0
Hi fascam:

9.99..... E307 is the largest number Excel can handle. I gave you a comprehensive formula (meaning whether there is a numeric entry in the list, or some text entry), the formula will pick up the last entry in the list.

Let us get to your particular case ...

1. what are the entries in your list, TEXT, or NUMBERS?

2. what is the range of your list ... do you know the starting cell, the ending cell, do you have a NAMED RANGE, or all you know is that it is in a particular column (in that case which COLUMN?)

If you can provide information for the two items above, we can pin down the formula more closely for your case.

Regards!

Yogi
 
Upvote 0

Forum statistics

Threads
1,223,460
Messages
6,172,345
Members
452,454
Latest member
MadamRedRabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top