Lookup the last value in a range and selecting the date

Sjodom

New Member
Joined
Jun 19, 2010
Messages
17
Hello,

I have a worksheet that has ranges of string values that contain a date within the first 5 characters. I am able to locate the date from the cell:

=MID(R10,1,5)

But I am now having difficulty locating the last cell in a general range with value. I have found macros to assist:

Selection.End(xlToRight).Select

But is then incompatible with the above cell formula.

Any ideas?
 

cpg84

Active Member
Joined
Jul 16, 2007
Messages
259
Hello,

I have a worksheet that has ranges of string values that contain a date within the first 5 characters. I am able to locate the date from the cell:

=MID(R10,1,5)

But I am now having difficulty locating the last cell in a general range with value. I have found macros to assist:

Selection.End(xlToRight).Select

But is then incompatible with the above cell formula.

Any ideas?
You could try:

{=index(x:x,max(if(x1:x100<>””,row(x1:x110))))}

where x = the range where the values are (x1:x100 would be the exact range - change to whatever you need it to be).

You will also need to validate the data (hence the {} around the formula) by pressing Ctrl + Shift + Enter at the same time.

This should return the last value within the range provided. I use that formula myself for my sport spreadsheets (basketball), so hopefully it works for you too. From the impressions I got from your post, this is what you are after.
 

Sjodom

New Member
Joined
Jun 19, 2010
Messages
17
Hello Cammy,

I think I follow the logic for the cell's code, but I get the #VALUE! message every time.

For your code:

=INDEX(11:11,MAX(IF(R11:DM11<>"",ROW(R11:DM11))))

To incorporate my code:

=MID(INDEX(10:10,MAX(IF(R10:DM10<>"",ROW(R10:DM10)))),1,5)

I tried with the {}:

{=INDEX(11:11,MAX(IF(R11:DM11<>"",ROW(R11:DM11))))}

and tried the Ctrl + Shift + Enter but nothing happened.

I have created a macro to do what I needed; however, it runs too slow. Your code is what I am looking for...

Thank you for your assistance!!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
Does something like...

=MID(LOOKUP(REPT("z",255),R10:DM10),1,5)+0

return the desired date?
 

Sjodom

New Member
Joined
Jun 19, 2010
Messages
17
Ha ha! Indeed! Very exciting. I have no idea how though.

Thank you!
 

CHStriker

New Member
Joined
Jun 21, 2010
Messages
15
This macro identifies the last used cell in a row

Code:
Sub IdLastCell()

Dim ix As Long
    ix = Worksheets("Sheet2").Range("a1").Row + Worksheets("Sheet2").UsedRange.Rows.Count
    LastRow = ix
    Worksheets("Sheet2").Range("a1").Cells(ix, 1) = 5
End Sub
This puts 5 in the next cell after the last used
 

cpg84

Active Member
Joined
Jul 16, 2007
Messages
259
Hello Cammy,

I think I follow the logic for the cell's code, but I get the #VALUE! message every time.

For your code:

=INDEX(11:11,MAX(IF(R11:DM11<>"",ROW(R11:DM11))))

To incorporate my code:

=MID(INDEX(10:10,MAX(IF(R10:DM10<>"",ROW(R10:DM10)))),1,5)

I tried with the {}:

{=INDEX(11:11,MAX(IF(R11:DM11<>"",ROW(R11:DM11))))}

and tried the Ctrl + Shift + Enter but nothing happened.

I have created a macro to do what I needed; however, it runs too slow. Your code is what I am looking for...

Thank you for your assistance!!
In my formula, INDEX(x:x...... x:x is not meant to be a number, it is meant to be a letter (referring to the column that the values are in). So maybe your code should be =

=INDEX(R:DM,MAX(IF(R11:DM11<>"",ROW(R11:DM11))))

See if that works (remember to press Ctrl + Shift + Enter to validate the data). Let me know how you go.

Sorry for the late reply, I live in Australia and have only just gotten to work and read my emails.
 

Sjodom

New Member
Joined
Jun 19, 2010
Messages
17
Thank you all!

Cammy -
This change makes sense. However, I am now seeing a #REF! error. The cell's formula is exactly how you used it, with the data validation.

Aladin -
This makes more sense. After playing with the formula and changing the values, I was able to see what sections effected the result. Specifically, the +0 at the end and the REPT("z",255). I am not sure why you chose "z" or 255, other values seemed to produce the same outcome.

CHStriker -
Thank you for the post for a macro. The macro version seems to run a tad bit slower than what I was looking for, but I appreciate the recommendation.

Again, thanks for the quick responses! I love this site!
 

cpg84

Active Member
Joined
Jul 16, 2007
Messages
259
Thank you all!

Cammy -
This change makes sense. However, I am now seeing a #REF! error. The cell's formula is exactly how you used it, with the data validation.

Aladin -
This makes more sense. After playing with the formula and changing the values, I was able to see what sections effected the result. Specifically, the +0 at the end and the REPT("z",255). I am not sure why you chose "z" or 255, other values seemed to produce the same outcome.

CHStriker -
Thank you for the post for a macro. The macro version seems to run a tad bit slower than what I was looking for, but I appreciate the recommendation.

Again, thanks for the quick responses! I love this site!
To be honest, I don't think my formula works if you are referring to more than one column. It should work perfectly if it was just one column.

I love this site too, I have received plenty of help with my own problems here for all of my sport spreadsheets. Now they are super awesome!
 

Forum statistics

Threads
1,081,513
Messages
5,359,225
Members
400,521
Latest member
smarty1995

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top