Quickly search for and delete entire row if certain character is found
 

Hello,

Im trying to find a macro that would quickly search for and delete rows
A, B, C, D and E when it finds a ! in column B. This ! exclamation mark
will be in a column with other miscelanious characters.

___A___B___C___D___E
1.|YZ |!!Z |YZ |YZ |YZ <--- exclamation mark ! found Delete A to E
2.|YZ |YZ |YZ |YZ |YZ
3.|YZ |YZ |YZ |YZ |YZ
4.|YZ |!!Z |YZ |YZ |YZ <--- exclamation mark ! found Delete A to E
5.|YZ |YZ |YZ |YZ |YZ

And so on.

Thanks,
Brian


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

Quickly search for and delete entire row if certain character is found
 

Bkbri wrote:
Im trying to find a macro that would quickly search for and delete rows
A, B, C, D and E when it finds a ! in column B. This ! exclamation mark
will be in a column with other miscelanious characters.


With Range("A1").CurrentRegion
.AutoFilter
.AutoFilter 2, "*!*" ' show only rows where B contains !
If .Columns(1).SpecialCells(xlVisible).Count1 Then
' not just the header so there are some to delete
.Offset(1).Resize(.Rows.Count-1).SpecialCells(xlVisible).Delete _
Shift:=xlUp
End If
.AutoFilter
End With

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

Quickly search for and delete entire row if certain character is found
 

Thank you very much it works great

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