Sub filterData()
Dim cRows As Long
Dim rng As Range
Dim testFormula As String

Application.ScreenUpdating = False

Columns("G:G").Insert
Rows(1).Insert
Cells(1, "G").Value = "Temp"

With ActiveSheet
cRows = .Cells(.Rows.Count, "F").End(xlUp).Row
testFormula = "=IF(F2=""a"",""Y"" ,"""")"
'create a test formula
.Cells(2, "G").Formula = testFormula
'copy the formula down all rows
.Cells(2, "G").AutoFill Destination:=.Range(.Cells(2, "G"), _
.Cells(cRows, "G"))
Set rng = .Range("G:G")
rng.AutoFilter Field:=1, Criteria1:="Y"

End With

rng.SpecialCells(xlCellTypeVisible).EntireRow.Dele te

Columns("G:G").Delete

Application.ScreenUpdating = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CPower " wrote in message
...
Hi guys,

is there a way of deleting rows, on the basis that a cell in the
particular rows, contains a word like "closed" or "suspended"

e.g rows c4 to c450 contains a, b, c, d, e and i want to delete all the
rows containing "a" in that particular column using macros.

can you please help me out??

Thanks Cathal.


---
Message posted from http://www.ExcelForum.com/