specialcells and error question

  • Thread starter Thread starter Abe
  • Start date Start date
A

Abe

I have the following code, where I want to set rnge to all the cells in
column two that have values in them.

Dim rnge as Range
On Error GoTo nothingIncol2:
Set rnge =
Worksheets("SaveLoad").Range("B:B").SpecialCells(xlCellTypeConstants)
On Error GoTo 0

However, if there is nothing in the B column, it returns a Run-time
error: "No Cells where found".

Shouldn't the error GoTo catch this?

Thanks,

-Abe
 
Yes it should. Try removing the colon in the on error goto statement. Just
use

On Error GoTo nothingIncol2
' specialcells code.
On Error goto 0


Exit sub
NothingIncol2:
msgbox "None found
End sub
 
I removed the colon, but it still gives me an error.

Anything else? Maybe a different way to check for empty cells?

Thanks again Tom,

-Abe
 
If you just want to count

msgbox Application.CountBlank(Activesheet.usedrange)

But I have never had problems with the construct you show. So I suggest you
go into the VBE and then tools=>Options and in the general tab, make sure you
have

break on unhandled errors

select rather than break on all errors.
 
Back
Top