Only keep rows that contain
 

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.

Only keep rows that contain
 

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

For so many rows try the Filter or union example


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SITCFanTN" wrote in message ...
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.

Only keep rows that contain
 

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.



"SITCFanTN" wrote in message
...
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.

Only keep rows that contain
 

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



"SITCFanTN" wrote in message
...
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.

Only keep rows that contain
 

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
--
Data Hog


"JLGWhiz" wrote:

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



"SITCFanTN" wrote in message
...
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.


.

Only keep rows that contain
 

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_Knowles" wrote in message
...
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
--
Data Hog


"JLGWhiz" wrote:

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



"SITCFanTN" wrote in message
...
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.


.

Only keep rows that contain
 

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

"J_Knowles" wrote:

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
--
Data Hog


"JLGWhiz" wrote:

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



"SITCFanTN" wrote in message
...
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.


.

Only keep rows that contain
 

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.
--
Data Hog


"SITCFanTN" wrote:

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

"J_Knowles" wrote:

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