Errortrap for WorksheetFunction.CountA - "No cells were found"?
 

Hi,
I have a rng that I want to check if there are any hidden not empty
cells (- the visible cells are of no interest.) It work ok if there
exist non-empty hidden cells, but I get error when it can't find any
cells (some of the code in lines below).

OK, fine I thought - then I make an error trap and make GoTo if I get
an error ... but I can't make it work and I now I ask:

Check rng if hidden cells aren't empty - I CAN'T be the first to need
this - can anybody please give me a sugestion how to solve the task?
There might be another better approach, or am I on the right track?

On Error GoTo FormCheck
'check contants and formulas together
If
Application.WorksheetFunction.CountA(Union(Range(r ng).SpecialCells(xlCellTypeConstants,
23), Range(rng).SpecialCells(xlCellTypeFormulas, 23))) 0 Then
Set rngconform = Union(Range(rng).SpecialCells(xlCellTypeConstants,
23), Range(rng).SpecialCells(xlCellTypeFormulas, 23))
End If

... Other code ...
Exit Sub

FormCheck:
etc


/Regards
tysk

Errortrap for WorksheetFunction.CountA - "No cells were found"?
 

Dim rngForm as Range, rngConst as Range
Dim rngConForm as Range
On error Resume Next
set rngForm = rng.SpecialCells(xlFormulas,23)
set rngConst = rng.SpecialCells(xlConstants,23)
On Error goto 0
if not rngForm is nothing then
if rngConst is nothing then
set rngconform = rngForm
else
set rngconform = Union(rngForm, rngConst)
end if
elseif not rngConform is nothing then
set rngconform = rngConst
else
set rngConform = Nothing
End if

I didn't see anything in your code about visible or hidden cells.

--
Regards,
Tom Ogilvy


"tskogstrom" wrote:

Hi,
I have a rng that I want to check if there are any hidden not empty
cells (- the visible cells are of no interest.) It work ok if there
exist non-empty hidden cells, but I get error when it can't find any
cells (some of the code in lines below).

OK, fine I thought - then I make an error trap and make GoTo if I get
an error ... but I can't make it work and I now I ask:

Check rng if hidden cells aren't empty - I CAN'T be the first to need
this - can anybody please give me a sugestion how to solve the task?
There might be another better approach, or am I on the right track?

On Error GoTo FormCheck
'check contants and formulas together
If
Application.WorksheetFunction.CountA(Union(Range(r ng).SpecialCells(xlCellTypeConstants,
23), Range(rng).SpecialCells(xlCellTypeFormulas, 23))) 0 Then
Set rngconform = Union(Range(rng).SpecialCells(xlCellTypeConstants,
23), Range(rng).SpecialCells(xlCellTypeFormulas, 23))
End If

... Other code ...
Exit Sub

FormCheck:
etc


/Regards
tysk

Errortrap for WorksheetFunction.CountA - "No cells were found"?
 

Thank you for the help,

That is correct, I forgot to enclose the code part regarding hidden
cells.
/tysk



tskogstrom skrev:

Hi,
I have a rng that I want to check if there are any hidden not empty
cells (- the visible cells are of no interest.) It work ok if there
exist non-empty hidden cells, but I get error when it can't find any
cells (some of the code in lines below).

OK, fine I thought - then I make an error trap and make GoTo if I get
an error ... but I can't make it work and I now I ask:

Check rng if hidden cells aren't empty - I CAN'T be the first to need
this - can anybody please give me a sugestion how to solve the task?
There might be another better approach, or am I on the right track?

On Error GoTo FormCheck
'check contants and formulas together
If
Application.WorksheetFunction.CountA(Union(Range(r ng).SpecialCells(xlCellTypeConstants,
23), Range(rng).SpecialCells(xlCellTypeFormulas, 23))) 0 Then
Set rngconform = Union(Range(rng).SpecialCells(xlCellTypeConstants,
23), Range(rng).SpecialCells(xlCellTypeFormulas, 23))
End If

... Other code ...
Exit Sub

FormCheck:
etc


/Regards
tysk