PDA

View Full Version : Deleting the row that contain the text " Dismantle"



aligahk06
04-25-2010, 01:54 AM
Dear All,

Attached file is for ref.

As per text in column v i want a macro or code that if text "DISMANTLE" is encountered then delete the row.
else do nothing.
The column V contains the Text " Dismantle" and otherwise.
Note:- In filtering mode deleting the rows consisting dismantle is tedious and risky if the data is two huge.
So I'm looking alternative and safe option.
Please assist?

Rgds,
Aligahk06

mdmackillop
04-25-2010, 04:14 AM
I don't see any of your code to assist with (see here (http://www.vbaexpress.com/forum/showpost.php?p=211905&postcount=8)). If it helps, I would still use Autofilter in creating a solution.

Bob Phillips
04-25-2010, 05:08 AM
Sub DeleteData()
Dim LastRow As Long
Dim rng As Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, "V").End(xlUp).Row
Set rng = .Range("A9:X" & LastRow)
rng.AutoFilter Field:=22, Criteria1:="DISMANTLE"
On Error Resume Next
.Rows(9).Hidden = True
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rng Is Nothing Then rng.EntireRow.Delete
.Rows(9).Hidden = False
.Rows(9).AutoFilter Field:=22, Criteria1:="*"
End With
End Sub