Finding the next non-empty cell in a column

[insomniac53]

This has probably been asked a million times before, but... given a specific cell, how do you search down to find the next non empty cell in that column. For example, if A10 has contents "dog", A50 has contents "cat" and A60 has contents "horse", how would you search from A10 to the next non-empty cell (A50) or from A50 to the next non-empty cell (A60) bearing in mind that you don't know the contents - so you can't search for dog, cat or horse, just a filled cell. I would need to return the row number of the next non empty cell. So, searchfunction("A10") would return 50 and searchfunction("A50") would return 60. Hope that makes sense.
Thanks.

 

[OilEconomist]

@Peter_SSs I modified the code which works except that it doesn't work if I would like it to focus on column A, which is my goal in this exercise. It finds the next cell entry in any column. So how can I change it to where it focuses on Column A. Remember, I'm trying to avoid activating the sheet where I am trying to find the next blank cell.

Sub NextCellwValue()

'Dimensioning
    Dim NextFilled As Long
    Dim ShtName As String
    Dim RowStart As Long
    Dim ColNum As Long

'Code
    Sheets("SUMMARY").Activate
    Range("A1:B2").Clear
   
    ShtName = "DATA1"
    ColNum = 1
    RowStart = 7
    NextFilled = NextFilledF(ShtName, ColNum, RowStart)
    Range("A1").Value = ShtName
    Range("B1").Value = NextFilled
   
    ShtName = "DATA2"
    ColNum = 1
    RowStart = 7
    NextFilled = NextFilledF(ShtName, ColNum, RowStart)
    Range("A2").Value = ShtName
    Range("B2").Value = NextFilled


End Sub


'****************************************************************************************************
'This function find the next row of data in a sheet after a specified row

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

    Dim WkS As Worksheet
       
    Set WkS = ActiveWorkbook.Worksheets(ShtName)
       
    With WkS
            NextFilledF = WkS.Cells.Find(What:="*", After:=WkS.Cells(RowStart, ColNum), LookIn:=xlValues).Row
            If NextFilledF <= RowStart Then NextFilledF = 0
    End With
   
   

End Function

 

[Peter_SSs]

Does this do what you want?

Function NextFilledF(ShtName As String, ColNum As Long, RowStart As Long) As Long
  With Worksheets(ShtName).Columns(ColNum)
    NextFilledF = .Find(What:="*", After:=.Cells(RowStart), LookIn:=xlValues, SearchDirection:=xlNext).Row
    If NextFilledF <= RowStart Then NextFilledF = 0
  End With
End Function

If it is possible that the relevant column has nothing in it some error handling will be needed as the function would error in that circumstance.

 

[OilEconomist]

Does this do what you want?

Function NextFilledF(ShtName As String, ColNum As Long, RowStart As Long) As Long
  With Worksheets(ShtName).Columns(ColNum)
    NextFilledF = .Find(What:="*", After:=.Cells(RowStart), LookIn:=xlValues, SearchDirection:=xlNext).Row
    If NextFilledF <= RowStart Then NextFilledF = 0
  End With
End Function

If it is possible that the relevant column has nothing in it some error handling will be needed as the function would error in that circumstance.

Yes this works perfect!

 

[Peter_SSs]

Good news. Thanks for the confirmation.