Excel - .SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Asked By ward376 on 02-May-08 01:23 AM
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.

Cliff Edwards

Ron de Bruin replied on 29-Apr-08 03:10 PM
Hi Cliff

See this page


Regards Ron de Bruin
James_Thomlinso replied on 29-Apr-08 03:18 PM
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.

Jim Thomlinson
ward376 replied on 02-May-08 01:23 AM
Thanks Jim, I pretty much thought that was the case.

Cliff Edwards
Rick Rothstein \(MVP - VB\) replied on 29-Apr-08 03:38 PM
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