Find the First Non ZeroLength Cell in a Range

You import a large list of data with a lot of empty cells. Here is a function that evaluates a range of cells and returns the value of the first non-zero-length cell.

FirstNonZeroLength(Rng) The argument is

Rng—The range to search. Function example:

Function FirstNonZeroLength(Rng As Range) Dim myCell As Range FirstNonZeroLength = 0# For Each myCell In Rng

If Not IsNull(myCell) And myCell <> "" Then FirstNonZeroLength = myCell.Value Exit Function End If Next myCell

FirstNonZeroLength = myCell.Value End Function

Figure 4.9 shows the function on an example worksheet.

Figure 4.9

Find the value of the first non-zero-length cell in a range.

0 0

Post a comment