Copy row if column contains specified number Solved/Closed

Question

Hello, 
I have three columns of information, column C contains the site name and number e.g. Priory Park 4742. I currently use Find to search for the number, then copy and paste the row into new worksheet. Any ideas for code to automate this task searching the whole of column C and copying the row or rows that contain the entered number. It's important to copy all rows where the cell in column C has the number to check for duplicate numbers.

Thanks Vince Harper


Configuration: Windows 7 / Internet Explorer 8.0

rizvisa1 · Answer

Could you please upload a sample file with sample data, Macro, CONDITIONAL FORMAT formula etc on some shared site like https://authentification.site , http://wikisend.com/ ,https://accounts.google.com/ServiceLogin?passive=1209600&continue=https://docs.google.com/&followup=https://docs.google.com/&emr=1 http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too

rizvisa1 · Answer

Try this

Sub Button1_Click()

Dim strsearch As String, lastline As Integer, tocopy As Integer
Dim Cell As Range

    strsearch = CStr(InputBox("enter the string to search for"))
    
    lastline = Range("A65536").End(xlUp).Row
    
    Set Cell = Sheets("Sheet2").Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    If Cell Is Nothing Then
        j = 1
    Else
        j = Cell.Row + 1
    End If
    
    Set Cell = Nothing
    
    For i = 1 To lastline
    
        For Each c In Range("C" & i)
        
            If InStr(c.Text, strsearch) Then
                tocopy = 1
            End If
        
        Next c
        
        If tocopy = 1 Then
            Rows(i).Copy Destination:=Sheets(2).Rows(j)
            j = j + 1
        End If
        tocopy = 0
        
    Next i

End Sub

Copy row if column contains specified number

2 responses

Similar discussions

Subscribe To Our Newsletter!