How would I get the first non-empty cell in a column?

2
votes

If a cell is empty, I want it to look in the cell above. If that one is empty, it will look in the cell above that until it finds a non-empty cell. When it does, it should return it. It'll be a UDF called loopUp. Then, when I use it in a cell formula like so: =loopUp(D24), and the range D18:D24 is empty, it'll show what is in D17.

Here's some Psuedo:

Function loopUp(Cell)
    while Cell is empty
        Cell becomes cell above
    return cell

I am new to VBA. The thing I am stuck on is how to pass in the address of a cell rather than its value.

3
vbaexcel
Can you show us what have you tried and where are you stuck? – Siddharth Rout
have you tried writing the function yourself? If so, post what you tried and we can help you with what is not working. – Scott Holtzman
That's the logic which you added. Can you show the code that you actually wrote and also mention which part is not working... – Siddharth Rout
I am stuck on is how to pass in the address of a cell rather than its value Try like this Function loopUp(cl as range) – Siddharth Rout

3 Answers

4
votes

Following Siddharth's last suggestion, here is a way to tackle it.

Function LoopUp(rng As Range) As Variant

Dim intCnt As Long

LoopUp = "Nothing found"

For intCnt = rng.Row To 1 Step -1
    If Not IsEmpty(Cells(intCnt, rng.Column)) Then
        LoopUp = Cells(intCnt, rng.Column)
        Exit For
    End If
Next


End Function

UPDATE

This may be a better, cleaner, easier to understand solution. I tested it a bit, but someone may break it!

Function LoopUp(rng As Range) As Variant

If Not IsEmpty(rng) Then LoopUp = rng
Else
    If rng.End(xlUp).Row = 1 Then LoopUp = "Nothing found" Else LoopUp = rng.End(xlUp)
End If 

End Function
3
votes
Function LoopUp(rng as Range)
    With rng.Cells(1)
        If Len(.Value) > 0 Then
            Lookup = .Value
        Else
            LoopUp = .End(xlUp).Value
        End If
    End With
End Function

EDIT: there was some discussion around what should happen if there's no "non-empty" cell above the passed Range parameter. Since the OP didn't specify what should happen this case this just returns an empty response.

0
votes

the UDF will look like this:

Function loopUp(Cell)
  Do While IsEmpty(Cell)
    Set Cell = Cell.Offset(-1)
  Loop
  loopUp = Cell
End Function

Note, that function will be recalculated only when Cell itself is changed and not when you change the range above Cell (which the functions scans), To cope with that add a second dummy parameter which should be set to the range which you want the function to monitor to be recalculated (maybe the whole column for simplicity).