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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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