Getting the First and Last Non-Empty Value Ranges: A Technical Support Guide

Getting First and Last Non-Empty Value Ranges: A Technical Support Guide

In this article, we will discuss how to create two functions that can be used to get the first and last non-empty value ranges in a given range. This can be particularly useful when working with ranges that contain a mix of empty and non-empty values, and you want to quickly identify the start and end of the non-empty ranges.

Example Range

Let's consider the following range as an example:

This range contains a mix of empty and non-empty values, as shown below:

Getting the First Non-Empty Value Range

To get the first non-empty value range in the example range, we can use the following function:

Function GetFirstNonEmptyRange(ByVal rng As Range) As Range
    Dim firstNonEmpty As Range
    Set firstNonEmpty = rng.Find("*", LookIn:=xlValues, LookAt:=xlWhole)
    If Not firstNonEmpty Is Nothing Then
        Set GetFirstNonEmptyRange = firstNonEmpty
    Else
        Set GetFirstNonEmptyRange = Nothing
    End If
End Function

This function uses the Find method to search for the first non-empty cell in the given range. If a non-empty cell is found, the function returns a reference to that cell. If no non-empty cells are found, the function returns Nothing.

Getting the Last Non-Empty Value Range

To get the last non-empty value range in the example range, we can use the following function:

Function GetLastNonEmptyRange(ByVal rng As Range) As Range
    Dim lastNonEmpty As Range
    Set lastNonEmpty = rng.Find("*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
    If Not lastNonEmpty Is Nothing Then
        Set GetLastNonEmptyRange = lastNonEmpty
    Else
        Set GetLastNonEmptyRange = Nothing
    End If
End Function

This function is similar to the previous function, but it uses the Find method with the SearchDirection parameter set to xlPrevious to search for the last non-empty cell in the given range. If a non-empty cell is found, the function returns a reference to that cell. If no non-empty cells are found, the function returns Nothing.

• In this article, we discussed how to create two functions that can be used to get the first and last non-empty value ranges in a given range.
• We provided an example range and demonstrated how to use the Find method to search for the first and last non-empty cells in that range.
• We also provided sample code for the two functions, which can be used to get the first and last non-empty value ranges in any given range.

References

• Range.Find method
• XlDirection enumeration
• XlFindLookIn enumeration
• XlFindLookAt enumeration