Find specific values in a column

[amivshek]

I am wanting to make a macro or something that will look into a specific column and find the rows that have a specific value in that column and then either delete the other rows or copy these rows into a new worksheet. Thanks

[realniceguy5000]

Hi amivshek,

Maybe?

Option Explicit
Option Compare Text
Public Sub KeepRows()
Dim i As Long
Dim iLastRow As Long
Dim col As Range
Dim sLookfor As String
With ActiveSheet
Set col = Application.InputBox("Use mouse to select target column", Type:=8)
If Not col Is Nothing Then
sLookfor = InputBox("Look for what string?")
If sLookfor <> "" Then
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Not .Cells(i, col.Column).Value Like sLookfor Then
Rows(i).Delete
End If
Next i
End If
End If
End With
End Sub

Or

Option Explicit
Option Compare Text
Public Sub MoveRows()
Dim i As Long
Dim iLastRow As Long
Dim col As Range
Dim sLookfor As String
With ActiveSheet
Set col = Application.InputBox("Use mouse to select target column", Type:=8)
If Not col Is Nothing Then
sLookfor = InputBox("Look for what string?")
If sLookfor <> "" Then
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Not .Cells(i, col.Column).Value Like sLookfor Then
Else
Rows(i).Copy
Sheets(2).Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial (xlValues)
 
End If
Next i
End If
End If
End With
End Sub