Function to get a special cell needed

[leassaf]

Hi!
Without using VBA:
Is there a function in EXCEL that would return the first cell containing data (the first cell which is not blank)?

Thanks for all excel pro's,

Assaf

 

[SkipVought]

Hi,
I know of no such function. The Function that comes the closest is DGET, but it does not cut it!

How about a procedure -- one might use a Filter, if the data is in a column, to find ALL NonBlank cell/values and by inference, the first NonBlank cell/value.

Of course, a VBA solution bould be a snap - one could design a user-defined function...
I assumed that you wanted a row or column returned, but a value could have been returned just as easily

Function FirstNonBlank(rng As Range) As Long
    Dim lRowCount As Long, iColCount As Integer
    With rng
        lRowCount = .Rows.Count
        iColCount = .Columns.Count
        If lRowCount > 1 And iColCount = 1 Then
            If IsEmpty(Cells(.Row, .Column).Value) Then
                FirstNonBlank = Cells(.Row, .Column).End(xlDown).Row
            Else
                FirstNonBlank = Cells(.Row, .Column).Row
            End If
            Exit Function
        End If
        If lRowCount = 1 And iColCount > 1 Then
            If IsEmpty(Cells(.Row, .Column).Value) Then
                FirstNonBlank = Cells(.Row, .Column).End(xlToRight).Column
            Else
                FirstNonBlank = Cells(.Row, .Column).Column
            End If
            Exit Function
        End If
        FirstNonBlank = 0
    End With
End Function

Skip,
metzgsk@voughtaircraft.com

 

[ilses]

Hi,

Skip's right: there no such function.

You can use a formula though. The following works on data in a single column (haven't tested for other ranges). It assumes that the range is named "Data".

=ADDRESS(SMALL(IF(Data<>&quot;&quot;,ROW(Data),&quot;&quot,1),SMALL(IF(Data<>&quot;&quot;,COLUMN(Data),&quot;&quot,1))

It's an array formula, so use Control-Shift-Enter to enter the formula.

HTH


ilses