Is it Easy ?

[susan_voyce]

Hi,

I have a worksheet of 40000 rows. My question is,
How to delete rows , if "!" character is not found, in column A ?

Eg:-

Column A
-------------
test!d
microsoft
excel!
forums
internet

So, I want to delete the rows, where the column A don't have "!" symbo
in the word.

Please advice !

Susa

 

[Patrick Molloy]

Application.ScreenUpdating = False

For ThisRow = Range("A65000").End(xlUp).Row to 1 Step -1

If Instr(Cells(ThisRow,"A").Value,"!")=0 then

Rows(ThisRow).Delete

End If

Next

Application.ScreenUpdating = True

 

[Gord Dibben]

Susan

Make a copy of your worksheet. Right-click on sheet tab>Move or Copy>Create a
copy.

Select Column A and Data>Filter>Autofilter>Custom "Does not contain" "!" and
OK.

Select the rows returned then Edit>Go To>Special>Visible Cells only>OK

Edit>Delete>Entire Row.

When satisfied, save the workbook then delete the worksheet copy.

Gord Dibben Excel MVP

 

[Rob van Gelder]

Sub test()
Const cSearch = "<>*!*"

With ActiveSheet
.Range("A1").EntireRow.Insert
.Columns("A:A").AutoFilter Field:=1, Criteria1:=cSearch
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub