Multiple Selection and Pasting RRS feed

  • 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



    Thursday, June 30, 2016 7:18 PM

All replies

  • Re:  finding stuff
    Here is some generic code that may help.
    It loops until nothing is found.

    Sub AnotherTest()
    Dim C As Range
    Dim x As Long: x = 2
    Dim firstAddress As String

    With Worksheets(2).UsedRange
      Set C = .Find("Sludge", LookIn:=xlFormulas)
      If Not C Is Nothing Then
          firstAddress = C.Address
            C.EntireRow.Copy Destination:=Worksheets(3).Cells(x, 1)
            x = x + 1
            Set C = .FindNext(C)
            If C Is Nothing Then Exit Do
          Loop Until C.Address = firstAddress
      End If
    End With
    End Sub

    My commercial 'Find & List' Excel add-in (xl97 to xl2010) can be downloaded from...
    and tried out. - no ads, no cookies, no trackers, no registration
    It has about 1400 lines of code.
    Jim Cone
    Portland, Oregon USA

    • Edited by James Cone Wednesday, September 21, 2016 10:21 PM
    Friday, July 1, 2016 2:04 AM