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

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
298
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,274
Office Version
  1. 365
Platform
  1. Windows
Try this function instead.

VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,967
Messages
5,767,370
Members
425,409
Latest member
Whatisanexcel

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top