I have following macro that I am using to delete entire row which has
blank cells or cells with exclamation mark. How do I go about catching
the errors that might be generated. One error that I am running into is
when there are no cells with either blank or exclamation marks?

Sub RowDelete(abc)
Worksheets(abc).Select
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Range("A2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="=*!*", Operator:=xlAnd
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Selection.AutoFilter
End Sub

Thanks, Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=533738

Hi Jay,

try this

Columns("B:B").Select

'adding one row of code
Selection.Replace What:="=*!*", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

'your original code continues
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

and remove all the filtering

Ivan

well I need to delete the entire row in which the ! is part of
value....
so I have cells containing data like
!2343
!6543
5848
8934
and so on.... so I need to delete those two first row completely....
what you suggested only replaces ! with blank and leaves rest of the
value in cell... so the cell is not blank.
Also, if I have data with no blanks I still get an error... so I still
need to catch that error.

Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=533738

Hi Jay,

Sub RowDelete(abc)
Worksheets(abc).Select

'here change where to look for "!", I used "Selection"
with Selection
..Replace What:="*!*", Replacement:="", LookAt:=xlPart
..SpecialCells(xlCellTypeBlanks).Select
..EntireRow.Delete
end with

Columns("B:B").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete

End Sub

The idea is to replace cells containing "!" with blanks and then to
delete entire rows.

You don't need any filtering with this attitude.

If you still want to filter (for any other reason), use "on error
resume next" and "on error goto 0" statements in your code.



Regards,

Ivan

Sub RowDelete(abc)
Worksheets(abc).Select
Columns("B:B").Select
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
On Error goto 0

Range("A2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="=*!*", Operator:=xlAnd
On Error Resume Next
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
On Error goto 0
Selection.AutoFilter
End Sub

--
Regards,
Tom Ogilvy

"sa02000" wrote:


I have following macro that I am using to delete entire row which has
blank cells or cells with exclamation mark. How do I go about catching
the errors that might be generated. One error that I am running into is
when there are no cells with either blank or exclamation marks?

Sub RowDelete(abc)
Worksheets(abc).Select
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Range("A2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="=*!*", Operator:=xlAnd
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Selection.AutoFilter
End Sub

Thanks, Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=533738