.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 

Is there a test (and what is it?) that could be used to avoid using on
error resume next before a line like this?

..SpecialCells(xlCellTypeBlanks).count returns an error if there are no
blanks in the referred-to range.

Thanks!
Cliff Edwards

.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 

Hi Cliff

See this page
http://www.rondebruin.nl/specialcells.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"ward376" wrote in message ...
Is there a test (and what is it?) that could be used to avoid using on
error resume next before a line like this?

.SpecialCells(xlCellTypeBlanks).count returns an error if there are no
blanks in the referred-to range.

Thanks!
Cliff Edwards

.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 

Nope... Ultimatelty SpecialCells(xlCellTypeBlanks) should return a range
object. If there are no blanks then the range object is nothing and so the
properties and methods will error out...

dim rng as range

on error resume next
set rng = ..SpecialCells(xlCellTypeBlanks)
on error goto 0

if not rng is nothing then
...

This type of error handling is not a bad way to go. I am a big fan of the
idea that the error handler is not there to cover up bad code. It is a tool
that should be used very judisciously. If you really wanted to you could
write a simple function that counted blank cells in and return true or false
to avoid the need for the error handler but that may be more work than it is
worth and it would certainly be extra overhead during exectution.
--
HTH...

Jim Thomlinson


"ward376" wrote:

Is there a test (and what is it?) that could be used to avoid using on
error resume next before a line like this?

..SpecialCells(xlCellTypeBlanks).count returns an error if there are no
blanks in the referred-to range.

Thanks!
Cliff Edwards

.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 

Thanks Jim, I pretty much thought that was the case.

Cliff Edwards

.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 

Maybe something like this?

Dim YourRange As Range
.....
.....
Set YourRange = Range("A1:B3")
If Application.WorksheetFunction.CountA(YourRange ) < YourRange .Count Then
' SpecialCells will not be Nothing
.....
.....

Rick


"ward376" wrote in message
...
Is there a test (and what is it?) that could be used to avoid using on
error resume next before a line like this?

.SpecialCells(xlCellTypeBlanks).count returns an error if there are no
blanks in the referred-to range.

Thanks!
Cliff Edwards