CODEWITHSUNDEEP
vbaexcel
tyjkenn
tyjkenn

Reputation: 719

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

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.

Upvotes: 2

Views: 3048

Answers (3)

Tim Williams
Tim Williams

Reputation: 166181

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.

Upvotes: 3

Scott Holtzman
Scott Holtzman

Reputation: 27249

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

Upvotes: 4

panda-34
panda-34

Reputation: 4209

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).

Upvotes: 0

Related Questions