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.

 

[Peter_SSs]

It isn't clear whether you are looking for a worksheet formula or vba. A worksheet formula to find next non-empty row after A10 would be

=MATCH(TRUE,INDEX(A11:A100<>"",0),0)+ROW(A10)

(I'm wondering what you are going to use that result for as there may be a more direct way to that next result.)

 

[Peter_SSs]

If you were looking for a UDF, you could try this. It returns the next non-empty row in the column or 0 if there is no subsequent non-empty cell in the column.
It assumes rStart is a single-cell range.

Function NextFilled(rStart As Range) As Long
  NextFilled = rStart.EntireColumn.Find(What:="?*", After:=rStart, LookIn:=xlValues).Row
  If NextFilled <= rStart.Row Then NextFilled = 0
End Function

 

[insomniac53]

It isn't clear whether you are looking for a worksheet formula or vba. A worksheet formula to find next non-empty row after A10 would be

=MATCH(TRUE,INDEX(A11:A100<>"",0),0)+ROW(A10)

(I'm wondering what you are going to use that result for as there may be a more direct way to that next result.)

Thank you - and yes, I forgot to say that this is a UDF in VBA. What I need to do is, given a cell where there definitely IS a value, count down the number of cells in that column until a non-blank (not non-zero) cell is reached. I will have a go at converting your Excel code to VBA.

 

[Peter_SSs]

I will have a go at converting your Excel code to VBA.

Did you see post #3 ?

 

[insomniac53]

Did you see post #3 ?

I did, but only after I'd posted. Your VBA code works well and I am busy integrating it into my application.
Thanks again. Much appreciated.

 

[Peter_SSs]

I did, but only after I'd posted. Your VBA code works well and I am busy integrating it into my application.
Thanks again. Much appreciated.

OK, great.

 

[OilEconomist]

Hi @Peter_SSs. Thank so much for your continued assistance on this site.

I used the code as is and also modified it directly into a sub and worked both ways. One thing I wanted to ask is if you do not want to activate the sheet where you are seeking the NextFilled cell in a particular column, how can I change your code?

I tried the following and it gave me the error "Run-time error '91': Object variable or With block variable not set."

Function NextFilledF(ShtName As String, rStart As Range) As Long

    With Sheets(ShtName)       
            NextFilledF = rStart.EntireColumn.Find(What:="?*", After:=rStart, LookIn:=xlValues).Row
            If NextFilledF <= rStart.Row Then NextFilledF = 0
    End With
    

End Function

I believe the issue is that I need to set a with for the range rStart, but then how do I handle rStart within the line item? When I do the following I get the same error "Run-time error '91': Object variable or With block variable not set."

 Function NextFilledF(ShtName As String, rStart As Range) As Long

    With Sheets(ShtName)
        With rStart
            NextFilledF = .EntireColumn.Find(What:="?*", After:=rStart, LookIn:=xlValues).Row
            If NextFilledF <= rStart.Row Then NextFilledF = 0
        End With
    End With
    
End Function

 

[Peter_SSs]

If you are using a function, how exactly are you using it in a workbook?
Or else can you upload a small dummy file with no sensitive data and with the failing function used in it to DropBox, One Drive, Google Drive etc and post a publicly shared link here?

 

[OilEconomist]

If you are using a function, how exactly are you using it in a workbook?
Or else can you upload a small dummy file with no sensitive data and with the failing function used in it to DropBox, One Drive, Google Drive etc and post a publicly shared link here?

Thanks @Peter_SSs for your response. I am trying to avoid activating the sheet as I would like to create a universal function whenever I need it in the future. Here is an example, with a summarized version of the code. I tested it and I get the error ""Run-time error '91': Object variable or With block variable not set."

Sub NextCellwValue()

'Dimensioning
    Dim NextFilled As Long
    Dim ShtName As String
    Dim Rng As Range
    Dim rStart As Range
  
  
'Code
    Sheets("SUMMARY").Activate
  
    ShtName = "DATA1"
    Set Rng = Range("A:A")
    Set rStart = Range("A7")
    NextFilled = NextFilledF(ShtName, Rng, rStart)
    Range("A1").Value = ShtName
    Range("B1").Value = NextFilled
  
    ShtName = "DATA2"
    Set Rng = Range("A:A")
    Set rStart = Range("A7")
    NextFilled = NextFilledF(ShtName, Rng, rStart)
    Range("A2").Value = ShtName
    Range("B2").Value = NextFilled


End Sub


'****************************************************************************************************
'This function find the next row of data in a range

Function NextFilledF(ShtName As String, Rng As Range, rStart As Range) As Long

    With Sheets(ShtName)
        With Rng.EntireColumn
            NextFilledF = .Find(What:="?*", After:=rStart, LookIn:=xlValues).Row
            If NextFilledF <= rStart.Row Then NextFilledF = 0
        End With
    End With
  
End Function