Modifying custom function to find second last instead of last

[BarinaX]

Hi all,


I've been using this function to find the last value in column. Now I would need it modified to find second to last instead. How do I change it?

Function LASTINCOLUMN(rng As Range)
    Dim LastCell As Range
    Application.Volatile
    With rng.Parent
        With .Cells(.Rows.Count, rng.Column)
            If Not IsEmpty(.Value) Then
                LASTINCOLUMN = .Value
            ElseIf IsEmpty(.End(xlUp)) Then
                LASTINCOLUMN = " "
            Else
                LASTINCOLUMN = .End(xlUp).Value
            End If
        End With
    End With
End Function

 

[DeBeuz]

Hi,

Instead of saving .value, I suggest you save .row. That way, you can set LASTINCOLUMN to cells(LASTINCOLUMNrow-1,rng.Column).Value to get the value before the last value in the column.

Does this work for you?

Paul

 

[BarinaX]

Sorry, I have trouble understanding what you are suggesting :/

 

[Rick Rothstein]

See if this function works for you...

Function SecondToLastValue(Rng As Range)
  Dim X As Long, LastRow As Long
  Application.Volatile
  If Rng.Columns.Count = 1 Then
    LastRow = Cells(Rows.Count, Rng.Column).End(xlUp).Row
    For X = LastRow - 1 To 1 Step -1
      If Len(Cells(X, Rng.Column).Value) Then
        SecondToLastValue = Cells(X, Rng.Column).Value
        Exit Function
      End If
    Next
  End If
End Function

 

[BarinaX]

Sorry, that only gives me zero as a result. I presume that this is because the data column has empty cells in between number cells? So that the function you posted only makes the search based on first empty, not the last filled cell.

 

[Rick Rothstein]

Sorry, that only gives me zero as a result.

Okay, see if this modification to my function works for you or not...

Function SecondToLastValue(Rng As Range)
  Dim X As Long, LastRow As Long
  Application.Volatile
  If Rng.Columns.Count = 1 Then
    LastRow = Rng.EntireColumn.Find("*", , xlValues, , xlRows, xlPrevious).Row
    For X = LastRow - 1 To 1 Step -1
      If Len(Cells(X, Rng.Column).Value) Then
        SecondToLastValue = Cells(X, Rng.Column).Value
        Exit Function
      End If
    Next
  End If
End Function