Last five entries

[ExcelRoy]

hi,

hopefully someone can help me out here, im a little stuck for a formula?

i have a range K28:K97 with numbers in each cell, now starts at row 28, but every week or so a new number is added upto a maximum of 97 (sometimes not that many)

what i would like is a formula to show only the last 5 entries

hope someone can help me out here

thanks in advance

 

[ExcelRoy]

Hi Domenic,

As requested

Private Sub CommandButton1_Click()
rows("9:97").Hidden = Not rows("9:97").Hidden
End Sub

 

[Domenic]

Try...

Private Sub CommandButton1_Click()
Rows("9:97").Hidden = Not Rows("9:97").Hidden
[COLOR="Red"]Application.Calculate[/COLOR]
End Sub

Public Function MultiConCatLast5(ByRef rRng As Range, Optional ByVal sDelim As String = "") As String
    [COLOR="Red"]Application.Volatile[/COLOR]
    Dim i As Long
    Dim j As Long
    j = 0
    For i = 1 To rRng.Count
        If rRng(i) <> "" Then
            j = j + 1
            MultiConCatLast5 = MultiConCatLast5 & sDelim & rRng(i).Text
            If j = 5 Then Exit For
        End If
    Next i
    MultiConCatLast5 = Mid(MultiConCatLast5, Len(sDelim) + 1)
End Function

 

[ExcelRoy]

Perfect, works a dream, thanks

Just curious, but what does the application.volatile mean and do?

 

[Domenic]

Perfect, works a dream, thanks

You're welcome!

Just curious, but what does the application.volatile mean and do?

It ensures that the function is recalculated whenever a calculation occurs in any cells on the worksheet. Apparently, when the rows are hidden, it triggers a re-calculation. However, for some reason, the calculation does not get completed, hence the function returns #VALUE!. By placing Application.Calculate within the event macro, it forces a re-calculation. Since Application.Volatile is included in the function, it re-calculates and returns the desired result.

 

[Domenic]

Actually, I meant to say...

It ensures that the function is recalculated whenever a calculation occurs in any cells on the worksheet. Apparently, when the rows are hidden, it triggers the function to re-calculate. However, for some reason, the calculation does not get completed, hence the function returns #VALUE!. By placing Application.Calculate within the event macro, it forces a re-calculation. Since Application.Volatile is included in the function, it re-calculates and returns the desired result.