How to locate the last row of cells in a range which matches using VBA?

user_15173572 0
Viewed 367

There is one column in a table where names of factories are shown but I only need the data for a specific factory name(let's say factory "Australia").

My idea is to locate the first and last rows which match because the data for the same factory are always presented in a consecutive manner. In this way, I can get the range of cells which match up to my search.

Locating the first matched row position is quite easy but I get stuck in getting the last matched row position.

Here is the code regarding this section:

Sub Search()

Dim sh As Worksheet
Dim searchStr As String
Dim lastRow As Long, firstRow as Long
Dim tableRange As range

Set sh = Worksheets("Total order")
searchStr = "Australia"
Set tableRange = sh.range("B:B").Find(what:=searchStr, LookIn:=xlValues, lookat:=xlWhole)
firstRow = tableRange.Row

End Sub

An example of the table dealt with:

enter image description here

vba excel
1 Answers

Refer to the Range from the Cell of the First to the Cell of the Last Occurrence of a String in a Column

A Side Note

  • The Range.Find method is kind of tricky. For example, you may not be aware that in your code the search starts from cell B2 (which is even preferable in this case), and using xlValues may result in undesired results if rows are hidden (probably not important).

Usage

  • Using the function, according to the screenshot, you could (after searchStr = "Australia") use:

    Set tableRange = refRangeFirstLast(sh.Columns("B"), searchStr)

    to refer to the range B4:B7, or use:

    Set tableRange = refRangeFirstLast(sh.Columns("B"), searchStr).Offset(, -1).Resize(, 4)

    to refer to the range A4:D7.

The Code

Function refRangeFirstLast( _
    ByVal ColumnRange As Range, _
    ByVal SearchString As String) _
As Range
    If Not ColumnRange Is Nothing Then
        With ColumnRange
            Dim FirstCell As Range: Set FirstCell = _
                .Find(SearchString, .Cells(.Cells.Count), xlFormulas, xlWhole)
            If Not FirstCell Is Nothing Then
                Dim LastCell As Range: Set LastCell = _
                    .Find(SearchString, , xlFormulas, xlWhole, , xlPrevious)
                Set refRangeFirstLast = .Worksheet.Range(FirstCell, LastCell)
            End If
        End With
    End If
End Function

Sub refRangeFirstLastTEST()
    Const SearchString As String = "Australia"
    Dim ColumnRange As Range
    Set ColumnRange = ThisWorkbook.Worksheets("Total order").Columns("B")
    Dim rg As Range: Set rg = refRangeFirstLast(ColumnRange, SearchString)
    If Not rg Is Nothing Then
        Debug.Print rg.Address
    Else
        MsgBox "The reference could not be created.", vbExclamation, "Fail?"
    End If
End Sub
user_98140690
Related