# Find cell with lowest last number

#### Lewiskj

##### New Member
Hard to explain what I'm trying to do in the title.

There is a link to the screenshot below, I'm having problems trying to add the image to this post

In column F: i want to find the cell with the lowest last number.
In this case it is F1791 as highlighted.
The last number could be anything from 1 digit to 5 digits long

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Rick Rothstein

##### MrExcel MVP
What do you meant by "find the cell"... do you want the cell selected, colored, its row displayed in a MessageBox, something else?

#### Lewiskj

##### New Member
What do you meant by "find the cell"... do you want the cell selected, colored, its row displayed in a MessageBox, something else?

Its row displayed in a MessageBox would be great.

What would then occur if there are more than one number sharing the lowest?

#### Rick Rothstein

##### MrExcel MVP
What would then occur if there are more than one number sharing the lowest?
What do you want to happen?

#### Lewiskj

##### New Member
What do you want to happen?

If there's more than 1 row sharing the lowest last number it would be helpful to display them all in a message box.

Thanks for taking the time to help.

#### Lewiskj

##### New Member
OK I've finally worked out how to post a screenshot, this is where I'm hoping to get a message box to tell me the the row which contains the lowest last number. In this case its E1791 which ends in 5135, but these final numbers could be from 1 digit up to 5 digits

F
17871067, 1144, 1165, 1308, 1465, 1921, 2317, 2578, 2753, 3107, 3228, 3301, 4151, 4203, 5031, 5370, 6342, 6361,
1788622, 994, 1139, 1144, 1329, 1573, 1707, 1898, 2027, 2613, 2838, 3000, 5168, 5878, 6178, 6378, 6509, 6598,
1789681, 1027, 1144, 1165, 1279, 1366, 1485, 1882, 1898, 2350, 3145, 3639, 3656, 4051, 4098, 5031, 5551, 5743, 6000, 6221, 6460, 6598,
17903033, 3220, 3639, 4096, 4200, 4455, 4601, 5670, 6221, 6378, 6460, 6598,
17913773, 3807, 4098, 4424, 4750, 5135,
17923033, 3145, 3372, 4001, 4018, 4079, 4098, 5289, 5670, 6111, 6166, 6194,
17935670, 6756,
17946186, 6342, 6648,
1795259, 333, 401, 1289, 1292, 1641, 1836, 1920, 2268, 2411, 2562, 2587, 2702, 3134, 3407, 3864, 4500, 4882, 5025, 5437, 5506, 5717, 6316, 6626, 6713,
1796145, 420, 555, 656, 917, 1289, 1575, 1641, 2156, 2441, 2933, 4570, 4645, 4882, 5684, 6014, 6072, 6593,
1797350, 542, 849, 1099, 1112, 1162, 1836, 2026, 4216, 4562, 4876, 4929, 5941, 6014,

<tbody>
</tbody>
Combinations 3 of 47

#### Rick Rothstein

##### MrExcel MVP
If there's more than 1 row sharing the lowest last number it would be helpful to display them all in a message box.
Give this macro a try...
Code:
``````Sub LowestLastNumber()
Dim R As Long, LastRow As Long, MinNum As Long, RowNum As Variant, LastNum As Variant, Data As Variant
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
Data = Range("F1:F" & LastRow)
MinNum = Evaluate("MIN(0+TRIM(LEFT(RIGHT(SUBSTITUTE(F2:F7,"","",REPT("" "",100)),200),100)))")
For R = 1 To UBound(Data)
If Data(R, 1) Like "*," Then
LastNum = Split(Data(R, 1), ",")
LastNum = LastNum(UBound(LastNum) - 1)
If LastNum = MinNum Then RowNum = RowNum & ", " & R
End If
Next
RowNum = Mid(RowNum, 3)

MsgBox "Minimum Number Row(s): " & RowNum

End Sub``````

#### Lewiskj

##### New Member
Give this macro a try...
Code:
``````Sub LowestLastNumber()
Dim R As Long, LastRow As Long, MinNum As Long, RowNum As Variant, LastNum As Variant, Data As Variant
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
Data = Range("F1:F" & LastRow)
MinNum = Evaluate("MIN(0+TRIM(LEFT(RIGHT(SUBSTITUTE(F2:F7,"","",REPT("" "",100)),200),100)))")
For R = 1 To UBound(Data)
If Data(R, 1) Like "*," Then
LastNum = Split(Data(R, 1), ",")
LastNum = LastNum(UBound(LastNum) - 1)
If LastNum = MinNum Then RowNum = RowNum & ", " & R
End If
Next
RowNum = Mid(RowNum, 3)

MsgBox "Minimum Number Row(s): " & RowNum

End Sub``````

Message box appears but doesn't give a row number

#### Rick Rothstein

##### MrExcel MVP
Message box appears but doesn't give a row number

Do your cells end with a comma, again, as your example showed?

Last edited:

#### Lewiskj

##### New Member

yes exactly as the example shows

At the end of every row there is a space after the last comma, not sure if this will effect anything that you're code is looking for.

Replies
18
Views
196
Replies
5
Views
73
Replies
9
Views
111
Replies
3
Views
200
Replies
3
Views
158

1,187,065
Messages
5,961,375
Members
438,539
Latest member
muimonk

### 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.

### Which adblocker are you using?

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

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