Only keep rows that contain

[SITCFanTN]

I posted this question once, but didn't phrase it properly; let me try
again. I want to delete all rows that do not contain the text of T75TA in
column A. There are over 10,000 rows of data so this macro would really help
me out. Thank you.

 

[Ron de Bruin]

See this page
http://www.rondebruin.nl/delete.htm

For so many rows try the Filter or union example

 

[JLGWhiz]

I believe somebody posted code similar to the code below for you in the
other posting.

Sub delIfnot()
Dim sh As Worksheet, lr As Long
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
If InStr(Range("A" & i).Value,T75TA) = < 1 Then
Rows(i).Delete
End If
Next
End Sub

Try it and see if it does the job.

 

[JLGWhiz]

Search string needed to be in quotes.

Sub delIfnot()
Dim sh As Worksheet, lr As Long
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
If InStr(Range("A" & i).Value, "T75TA") = < 1 Then
Rows(i).Delete
End If
Next
End Sub

 

[J_Knowles]

JLGWhiz - I modified this code and it works for me.
If InStr(Range("A" & i).Value, "T75TA") = 0

Sub delIfnot()
Dim sh As Worksheet, lr As Long
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
If InStr(Range("A" & i).Value, "T75TA") = 0 Then
Rows(i).Delete
End If
Next
End Sub

I also was working an alternate solution and here is that macro:

Sub OnlyKeepT75A()
On Error Resume Next
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For A = lastrow To 2 Step -1
If IsError(WorksheetFunction.Find("T75TA", Cells(A, 1).Value, 1)) Then
Cells(A, 1).EntireRow.Delete
End If
Next
End Sub


HTH

 

[JLGWhiz]

The OP wanted to delete the row if T75TA was not found in col A. Filtering
would have worked just as well, but sometimes the person posting has other
things in mind.

 

[SITCFanTN]

This worked great, thank you so much...the 0 was the key! Happy Holidays

 

[J_Knowles]

Your welcome, I am curious if you tried the alternate macro to see which one
was the fastest. I'm thinking the Instr command will be faster than the
WorksheetFunction.Find. Let me know which one is the best.