.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

[ward376]

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

 

[Ron de Bruin]

Hi Cliff

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

 

[Jim Thomlinson]

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.

 

[ward376]

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

Cliff Edwards

 

[Rick Rothstein \(MVP - VB\)]

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