Trying to figure out how to only reference the visible row above

[Miles101]

Hello,

I'm using the following formula in Excel 2010.

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+(CONCATENATE(0.01))

What I would like to do, is to convert this formula to reference the first unhidden row above. Currently, it will just reference the row above weather it is hidden or not. I would like to be able to hide the rows above and have the results automatically change. This for creating a number reference down a column such as:

3.01
3.02
3.03

Let me know if you have any suggestions or questions.

Thank You

 

[Teeroy]

I don't know of any built in function that will do this but here is a User Defined Function that will. Use it like: = NextVis(A15) for example

Function NextVis(Target)
If VarType(Target) = vbString Then
    Set Target = Range(Target)
End If
Set rtest = Target.Offset(-1, 0)
Do
If Not rtest.EntireRow.Hidden Then
    NextVis = rtest.Value
    Exit Do
End If
If rtest.Row = 1 Then
    NextVis = rtest.Value
    Exit Do
End If
Set rtest = rtest.Offset(-1, 0)
Loop While NextVis = ""
End Function

If you haven't used UDFs before see How to Create a User Defined Function in Microsoft Excel or How do I write a simple User Defined Function (UDF) in Excel with VBA? – Computer Aided Finance - Excel, Matlab, Theta Suite etc. for a tutorial of how to add it.

 

[Miles101]

Thank you very much for the function. I've almost got everything to work, but the only issue I'm having now is that is is going two rows above the formula and not directly above.

=(NextVis(INDIRECT(ADDRESS(ROW()-1,COLUMN())))+(CONCATENATE(0.01))

Any suggestions on how to fixed this issue?

 

[Teeroy]

You shouldn't need the "INDIRECT(ADDRESS(ROW()-1,COLUMN()))". You just point to the cell above, or use R[-1]C if set up to use relative reference style.

 

[Miles101]

Still no go, the formula wants to reference 2 rows above. Just doing a simple test of =NextVis(A10) display the result from cell (A9). I have noticed that if I hide row A10 and above, then the formula appears to work correctly.


And I would also like not to have to reference the cell above but for it to automatically locate the cell above that isn't hidden. I see you mentioned using the following:

R[-1]C if set up to use relative reference style

But I didn't appear to have much luck with that.

I can you give you some more background on what exactly I'm trying to do. I have a spreadsheet with all of these steps listed out and number in sequential order column A. At the top of the spreadsheet there are questions the user's need to answer via a drop down menu provided via data validation. Depending on the answer they choose, the rows below will automatically hide and unhide the steps that meet their needs.

The issue is the numbers I have in column A are no longer sequential when the rows hide or unhide. What I would like to do is have the formula be able to automatically adjust the numbers so if only 3 rows out of 10 are displayed, you will see the numbers in column A adjust to list 1.01, 1.02, and 1.03.

I hope this helps.

Thank you again for any assistance you can provide.

 

[Teeroy]

I think I understand. From your description I thought you wanted the next visible from the cell referenced, but you want it from the cell containing the formula (i.e. if the cell referenced is visible return its value otherwise return the next visible).

Try (e.g. in A16) = NextVis2(A15)

Function NextVis2(Target)
If VarType(Target) = vbString Then
    Set Target = Range(Target)
End If
Set rTest = Target
Do
If Not rTest.EntireRow.Hidden Then
    NextVis2 = rTest.Value
    Exit Do
End If
If rTest.Row = 1 Then
    NextVis2 = rTest.Value
    Exit Do
End If
Set rTest = rTest.Offset(-1, 0)
Loop While NextVis2 = ""
End Function

 

[Jerry Sullivan]

Hi All,

To get the index numbers that Miles101 describes with that UDF, I believe it would be....

In A16, =NextVis2(A15)+0.01

Teeroy, If there is a potential for a large number of consecutive rows to be hidden, then the approach of iterating through each cell testing for the first visible cell would not be as efficient as using Range.Find().

For example...

In A16, =NextIndex(A15)

Function NextIndex(rPrecedent As Range)
    Dim rPrev As Range
    Const dIncrement As Double = 0.01

    
    With Application.Caller
        Set rPrev = Cells(1, .Column).Resize(.Row) _
            .Find("*", After:=.Cells(1), LookIn:=xlValues, _
                 SearchDirection:=xlPrevious, _
                SearchFormat:=False)
    End With


    If IsNumeric(rPrev.Text) Then
        NextIndex = rPrev.Text + dIncrement
    Else
        NextIndex = CVErr(xlErrValue)
    End If
End Function

If there are not more than a few consecutive hidden rows, then Teeroy's UDF would be faster.

FWIW, The indexing objective could probably be achieved without the use of VBA using the SUBTOTAL() function to differentiate hidden from visible cells.

The specifics of how to do that would depend on the layout the data and how the increment jumps from 1.01, 1.02, 1.03, to: 2.01, 2.02....

 

[Miles101]

Thank you Teeroy and JS411. Everything is now working great! I really appreciate the help.