VBA Code to Find the Previous Non-Empty Cell in a Column

[OilEconomist]

Thanks in advance for any assistance. I am getting a value of 0 for and it should return a value of 4. I am attempting to find the previous filled cell if I specify a row and column. This is a bit of a hybrid of this one: Finding the next non-empty cell in a column as I am trying to find the previous non-empty cell versus the next.

Option Explicit

Public Sub PrevFill()

'Dimensions
    Dim PrevFillCol As Long
    Dim ShtName As String
    Dim ColNum As Long
    Dim RowStart As Long
    
'Code
    ShtName = "Sheet1"
    ColNum = 1
    RowStart = 11
    
    PrevFillCol = PrevFillColF(ShtName, ColNum, RowStart)
    MsgBox PrevFillCol


End Sub


'****************************************************************************************************
'This function finds the previous filled cell in reference to a specified row in a specified column


Function PrevFillColF(ShtName As String, ColNum As Long, RowStart As Long) As Long

    With Worksheets(ShtName).Columns(ColNum)
        On Error GoTo 100
        PrevFillColF = .Find(What:="*", Before:=.Cells(RowStart), LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    End With

100:

End Function

 

[Peter_SSs]

Try this function instead.

Function PrevFillColF(ShtName As String, ColNum As Long, RowStart As Long) As Long
    Dim rFound As Range
    
    With Worksheets(ShtName).Columns(ColNum)
        On Error Resume Next
        Set rFound = .Find(What:="*", After:=.Cells(RowStart), LookIn:=xlValues, SearchDirection:=xlPrevious)
        On Error GoTo 0
        If Not rFound Is Nothing Then
          If rFound.Row < RowStart Then PrevFillColF = rFound.Row
        End If
    End With
End Function