Only keep rows that contain

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.
 
J

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.
 
J

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

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
 
J

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.
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top