# How to get the previous non-empty cells in the column

• ### Question

• Hi,

The bold values below in Row 2, 6, 7 and 10 are fixed.

I would like to know what is the formula to find the previous non-empty cell in the column. e.g, for Row 4, it should have the value b, Row 8 should have the value d as show below.

﻿﻿﻿
Monday, October 31, 2016 7:17 PM

• For numbers, you'd use

=LOOKUP(9.99999999999999E+307,A\$1:A3)

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Thursday, November 3, 2016 2:35 PM
Tuesday, November 1, 2016 3:43 PM

### All replies

• In A4, enter the formula

=LOOKUP(REPT("z",255),A\$1:A3)

You can copy A4 to other empty cells.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Monday, October 31, 2016 7:27 PM
• Thanks Hans, it works when it is in text format

Actually, what if it is a number?

Thanks

• Edited by Tuesday, November 1, 2016 1:22 PM
Tuesday, November 1, 2016 12:52 PM
• For numbers, you'd use

=LOOKUP(9.99999999999999E+307,A\$1:A3)

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Thursday, November 3, 2016 2:35 PM
Tuesday, November 1, 2016 3:43 PM
• Hi wkpli,

Thank Hans for the solid suggestion.

For custom function to achieve your requirement, I suggest you try to achieve your own function.

```//defination
Function getNonEmpty(r As Range)
Dim row As Integer
row = r.Rows.Count
For i = row To 1 Step -1
If r.CurrentRegion.Cells(i, r.Column) <> "" Then
getNonEmpty = r.CurrentRegion.Cells(i, r.Column).Value
Exit For
End If
Next i
End Function
//use
=getNonEmpty(A1:A4)```

Best Regards,

Edward

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Wednesday, November 2, 2016 5:54 AM