Selecting last five numbers in a selection

[Scott]

I have a series of data in the same row, (F5:AC5) containing anywhere from 1
to 24 numbers. In cells AD5:AH5, I want to determine the LAST five numbers
entered in the row of data (not smallest or largest, but simply the last
numbers that were entered in). There could be duplicate numbers, and blanks
in between cells of numbers. How would I go about this?

Thanks.

 

[Don Guillett]

try this idea
Sub lastfivenums()
mr = 5
For i = Cells(mr, Columns.Count).End(xlToLeft).Column To 6 Step -1
If Cells(mr, i) > 0 Then
ms = ms + Cells(mr, i)
mc = mc + 1
If mc = 5 Then Exit For
End If
Next i
MsgBox ms
End Sub

 

[Scott]

Will anything other than a macro work?

try this idea
Sub lastfivenums()
mr = 5
For i = Cells(mr, Columns.Count).End(xlToLeft).Column To 6 Step -1
If Cells(mr, i) > 0 Then
ms = ms + Cells(mr, i)
mc = mc + 1
If mc = 5 Then Exit For
End If
Next i
MsgBox ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[T. Valko]

What should happen if there aren't 5 numbers?

 

[Scott]

I would want it to return the numbers up to five numbers. So if there were
only three number for instance in the data set, I'd want to return all three.

 

[T. Valko]

Try this array formula** entered in AD5 and copied across to AH5:

=IF(COLUMNS($AD5:AD5)<=COUNT($F5:$AC5),INDEX($F5:$AC5,LARGE(IF($F5:$AC5<>"",COLUMN($F5:$AC5)),COLUMNS($AD5:AD5))-COLUMN($F5)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.