Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
You have to be more specific, what are you trying to achieve? – Sandeep Bansal Apr 12 '11 at 16:30
I want to select the value of a cell above an empty cell. – Jeremy Boyd Apr 12 '11 at 16:31
up vote 0 down vote accepted

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

share|improve this answer

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.

share|improve this answer
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. – Jeremy Boyd Apr 12 '11 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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.