Excel - Look up last value in a column

  Craig.m 12:01 18 Feb 2005
Locked

I have a cell that I want to always reflect the value of the last entry in another column. How can I get the cell to return the last number of the column, it is always being added to so will move further down as more data is added.

I have looked through the functions but can't find one that appears to give me what I am after. Would prefer to use a function and not a bit of VBA or a macro

Craig

I know of no function but assuming that the cell you wish the information to be in is A1 and that the column is B then the following small bit of code placed in the code of the worksheet itself will do the job

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

[A1] = Range("B" & Cells(Rows.Count, 2).End(xlUp).Row)

End Sub

Just change the A1 to the cell and the figure 2 to the column number.

HTH

Private Sub Worksheet_SelectionChange(ByVal Target As Range)



[A1] = Range("B" & Cells(Rows.Count, 2).End(xlUp).Row)


End Sub

  Craig.m 12:09 18 Feb 2005

Hi Whisperer

I will have a go at that solution.

Craig

  VoG II 12:13 18 Feb 2005

A non VBA alternative. Say you are looking for the last value in column E, there are no gaps in column E, and the maximum rows is 100

=INDEX(E1:E100,COUNTA(E1:E100))

  Craig.m 12:23 18 Feb 2005

Whisperer:

Inserting VBA - thought I knew how but failed dismally on that one, should have been watching when you were giving lessons to Bramble.

VoG:

Tried yours too and it returned the usual #ref.

Will have another try at the thing later.

Craig

  Craig.m 12:39 18 Feb 2005

Decided not to be beaten and went in again, Whisperer, your second answer works. The data is in column E (wonder how VoG knew that) so I changed the B to E, A1 now reflects the last figure in E but only if it coincides with the last entry in B. This is not an issue as the two will always finish with an entry at the same time.

Just as a learning exercise, what gives it the reference to B?

Craig

Hi Craig,

I did not recognise you immediately.

In the code below the A1 is the cell in which you are going to store the value of the last cell in the desired column.

After the = sign we are looking for the last cell that contains data in the correct column. In your case you have stated that it is column E (or 5) so I have amended the code to reflect this so you can compare with the original.

[A1] = Range("E" & Cells(Rows.Count, 5).End(xlUp).Row)

Hope that helps and best wishes to Br****

Gordon

VoG™,

Thanks again for widening my experience of functions, all I have to do now is to marry up Index and Match!

Craig,

I have tried VoG™ code and it works for me with the caveat that there can be no gaps in the array, it can be modified to be

=INDEX(E:E,COUNTA(E:E))

but still needs no gaps in column E.

  VoG II 19:55 18 Feb 2005

Oh, sorry, I did not realise we were carrying on here:

VBA solution click here


Workshhet solution (I do not like this) click here

  Craig.m 23:33 18 Feb 2005

Peculiar, I did think the 2 may need amending to a 5 but when I changed it the cell sat there blank. At least I have a working solution now, thanks

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment utiliser Live Photos ?