need macro to delete rows contain specific text

[zaidan]

dear all,

I need help : i need macro to delete rows that contain specific text at the middle of the data. In my case : i want to delete rows that contain PPAA in the middle of the data, example :

Data :
2011-05-27 00:19Z 301979885 FTI01 AAXX 27004 97460 11459 70000
2011-05-27 00:36Z 301981102 FTI01 AAXX 27004 97460 11459 70000
2011-05-27 00:46Z 301982166 FTI01 PPAA 77001 97460 55385 14007
2011-05-27 00:46Z 301982171 FTI01 PPAA 77001 97460 55385 14007
2011-05-27 02:55Z 301987965 FTI01 AAXX 27034 97460 31458 72604
2011-05-27 00:48Z 301982299 FTI01 PPAA 77001 97460 90/12 00000

Result I want :
2011-05-27 00:19Z 301979885 FTI01 AAXX 27004 97460 11459 70000
2011-05-27 00:36Z 301981102 FTI01 AAXX 27004 97460 11459 70000
2011-05-27 02:55Z 301987965 FTI01 AAXX 27034 97460 31458 72604

Thanks for any help.

 

[GTO]

Does your data look something like:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	5/27/2011	00:19Z	301979885	FTI01	AAXX	27004	97460	11459	70000
2	5/27/2011	00:36Z	301981102	FTI01	AAXX	27004	97460	11459	70000
3	5/27/2011	00:46Z	301982166	FTI01	PPAA	77001	97460	55385	14007
4	5/27/2011	00:46Z	301982171	FTI01	PPAA	77001	97460	55385	14007
5	5/27/2011	02:55Z	301987965	FTI01	AAXX	27034	97460	31458	72604
6	5/27/2011	00:48Z	301982299	FTI01	PPAA	77001	97460	90/12	0
Sheet2

Excel 2003
...or ???

 

[zaidan]

No, my data in one column (column A).

 

[Roli001]

Try this:

Sub deletedata()

i = 1
Do Until IsEmpty(Cells(i, 1))
    If InStr(Cells(i, 1), "PPAA") Then
        Cells(i, 1).Delete Shift:=xlUp
        i = i - 1
    End If
    i = i + 1
Loop

End Sub

 

[GTO]

Okay, if like:

Excel Workbook
	A
1	Data :
2	2011-05-27 00:19Z 301979885 FTI01 AAXX 27004 97460 11459 70000
3	2011-05-27 00:36Z 301981102 FTI01 AAXX 27004 97460 11459 70000
4	2011-05-27 00:46Z 301982166 FTI01 PPAA 77001 97460 55385 14007
5	2011-05-27 00:46Z 301982171 FTI01 PPAA 77001 97460 55385 14007
6	2011-05-27 02:55Z 301987965 FTI01 AAXX 27034 97460 31458 72604
7	2011-05-27 00:48Z 301982299 FTI01 PPAA 77001 97460 90/12 00000
Sheet3

Excel 2003
Try:

Option Explicit
    
Sub exa()
Dim lLRow As Long
Dim lRow As Long
    
    With Sheet3 '<---CodeName OR sheet/tab name---> ThisWorkbook.Worksheets("Sheet3")
        lLRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        For lRow = lLRow To 2 Step -1 '<---Presumes a header row
            If InStr(1, .Cells(lRow, "A").Value, " PPAA ") Then
                .Rows(lRow).Delete
            End If
        Next
    End With
End Sub

Hope that helps,

Mark

 

[zaidan]

thanks for the answer it's work...