Only keep rows that contain

  • Thread starter Thread starter SITCFanTN
  • Start date Start date
S

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.
 
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.
 
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
 
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
 
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.
 
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.
 
Back
Top