How would I select a cell that is above an empty cell? I have a feeling it is going to be something super simple, but I haven't done any excel since 2004.

Here is a simple single column sheet.





I am hoping it would be a function and it should return data2, data4 and data4 in an array of sorts.

  • You have to be more specific, what are you trying to achieve? Apr 12, 2011 at 16:30
  • I want to select the value of a cell above an empty cell. Apr 12, 2011 at 16:31

3 Answers 3


Jeremy - I think you would need to write a simple macro function to do this:

Public Sub FillBlanks()
    Set myRange = Application.InputBox( _
        prompt:="Select a range", Type:=8)
    Dim prevCellValue
    For Each c In myRange.Cells
        If c.Value <> "" Then _
            prevCellValue = c.Value _
        Else: c.Value = prevCellValue
End Sub
Note that this subroutine would only work on single columns or rows. Josh


Go to the blank cell where you want your result, and just type "=" and then hit the up arrow. OR type "=" then type the column letter followed by the row number. For example, if the cell you would like to display the result in cell B5, you would click on the B5 cell and type "=B4" (without the quotes) then press enter. This would give you the result of the cell B4 in the cell B5 that you have selected.

just go to where you want your result and type "=A2+A4+A6" This will add the results of the cells into the cell you have selected.

  • I don't want to do it manually, and I was hoping it would be a function that I could then run inside another sheet. Apr 12, 2011 at 16:35

This formula, array entered with control+shift+enter


will return this array


Depending on what you want to do with it, you will need to ignore the REF errors and process the non-errors. It also may be easier than that. If, for instance, the "data" is numbers that you want to sum, say starting in A2





and you want to return 110 (20+40+50), then


That only works if you have a blank cell above your data.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .