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

  • Thread starter Thread starter bkbri
  • Start date Start date
B

bkbri

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
 
Bkbri said:
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.
Click to expand...

With Range("A1").CurrentRegion
.AutoFilter
.AutoFilter 2, "*!*" ' show only rows where B contains !
If .Columns(1).SpecialCells(xlVisible).Count>1 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
 
Back
Top