Search and select multiple cells in vba

Question

I am trying to use a userforum to search specific keywords and to select all the cells containing that information and to copy and paste the entire row into another sheet. So far I only know how to select one cell/row at a time. This is what I have so far.

Private Sub CommandButton1_Click()
    Cells.Find(What:=searchbox1.Text, After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:= True, SearchFormat:=False).Activate
    ActiveCell.EntireRow.Select
    Selection.Copy
    sheets(2).activate

see my answer to [this question](http://stackoverflow.com/questions/38105313/deleting-rows-of-data-not-needed/38105606#38105606).... very similar concept — Scott Holtzman, Jun 30 '16 at 20:11

score 0 · Answer 1 · answered Jun 30 '16 at 22:57

Unless you are trying to filter through 100's of thousands of line, a simple loop will do the same job as the find.

Adapt this code to your workbook, it loops through the range you want to test against your textbox and copies every match to the range of your choice.

   Dim rng As Range
   Dim destRng As Range

   Set rng = Range("A1:A20")

  For Each r In rng
      If r.Value = searchbox1.Text Then
        If Not destRng Is Nothing Then
           Set destRng = Union(r.EntireRow, destRng)
        Else
           Set destRng = r.EntireRow
        End If
      End If
  Next r
      destRng.Copy sheets(2).Range("A1")

Search and select multiple cells in vba

1 Answers1