Excel - Look up last value in a column

  Craig.m 12:01 18 Feb 05

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


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.


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 05

Hi Whisperer

I will have a go at that solution.


  VoG II 12:13 18 Feb 05

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


  Craig.m 12:23 18 Feb 05


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


Tried yours too and it returned the usual #ref.

Will have another try at the thing later.


  Craig.m 12:39 18 Feb 05

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?


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****



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


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


but still needs no gaps in column E.

  VoG II 19:55 18 Feb 05

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 05

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.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac