Select Only Rows Matching Exact Text String-CodePudding

I have some data in Excel where I need to use VBA to select only rows (entire rows) that match specific text in any one of the columns per row. The data that will be copied into Excel is dynamic, such that the text I need to match won't always be in the same column and I have that part figured out, but the matching is selecting more rows than I want.

For simplicity, I want to match on the text "$ value 1" or "$ value 2" or "$ value 37", etc. The text that needs to be matched/found will always begin with "$ value" in this example (without quotes) and will end with any digit from 1 to 99999. I included a screenshot from Excel showing a basic example with 7 rows, but the actual data will be thousands of rows.

I have a VBA/macro that sort of works (below), but I'm running into an issue where I want to match on "$ value 1" and select only that specific row (i.e. first row in the example Excel data/screenshot), but it's also selecting rows that have "$ value 10" and "$ value 15" (2nd and 3rd rows in the example data). It's essentially selecting anything that BEGINS WITH "$ value 1". I want to match EXACTLY on the entire content of any cells where it finds "$ value 1". Likewise, the same thing happens if I want to select the row with "$ value 2", it also selects the row that has "$ value 25" in the example data.

When the macro runs, it asks a user to simply input any digit from 1 to 99999 and in the Instr() line I'm concatenating on the "$ value " portion before the digit that the user input. When a user inputs a 1 for example, I want the VBA code to only select the rows that match exactly on "$ value 1", which could be found in any of the columns that have data (happens to be in column C in the example data, but the same value could be in other columns in other rows). I don't want VBA to select the rows which have "$ value 10" or "$ value 15", etc. unless the user explicitly input 10 or 15.

I'm not sure if I need to do some kind of text comparison or if there's a better method, but being new to VBA I'm not sure how to accomplish what I need.

 Sub select_rows_based_on_match()

 Dim rng As Range
 Dim cell As Object
 Dim row As Range
 
 Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select
 Set rng = Selection
 
 strInput = InputBox("Enter a value to match on:")
 
 For Each cell In rng
 If InStr(cell.Text, ("$ value " & strInput)) Then
      If Not row Is Nothing Then
          Set row = Union(row, cell.EntireRow)
      Else
          Set row = cell.EntireRow
      End If
 End If
 Next
 If row Is Nothing Then
     MsgBox "There wwas no $ value " & strInput & " found"
 Else
     row.Select
 End If

 End Sub

CodePudding user response：

Using Match() would be faster than going cell-by-cell for each row:

Sub select_rows_based_on_match()
    Dim rng As Range, row As Range, matchedRows As Range, strInput

    strInput = InputBox("Enter a value to match on:")
    If Len(strInput) = 0 Then Exit Sub
    strInput = "$ value " & strInput
    
    Set rng = ActiveSheet.Range("A1").CurrentRegion

    For Each row In rng.Rows
        'if no match is found then Match returns an error value
        If Not IsError(Application.Match(strInput, row, 0)) Then
            If Not matchedRows Is Nothing Then
                Set matchedRows = Union(matchedRows, row)
            Else
                Set matchedRows = row
            End If
        End If
    Next row

    If matchedRows Is Nothing Then
        MsgBox "There was no '" & strInput & "' found"
    Else
        matchedRows.Select
    End If

End Sub