isempty works with a single variable or a single cell.
if you have a range variable, and it does not point to a range, then you
test this state using
Dim Global_ExclColRng as Range
If Global_ExclColRng is Nothing then
' no referencing a cell
Elseif Global_ExcelColRng.count = 1 then
if isempty(Global_ExcelColRng) then
' one cell, its empty
Else
' multicell range
bEmpty = True
for each cell in Global_ExcelColRng
if not isempty(cell) then
bEmpty = False
exit for
end if
Next
if bEmpty then
' all the cells are empty
End if
End if
if a multicell range, an alternative to looping the cells and checking
individually is to use the worksheet countA function
if application.CountA(Global_ExcelColRng) = 0 then
' all cells are empty
End if
It would work for both a multicell and single cell range - so you wouldn't
have to differentiate
However, if the variable is Nothing, you always need to check for that first
and not try to use it as a range if that is the case.
--
Regards,
Tom Ogilvy
"Stuart" wrote in message
...
Am having problems in testing for the values held by range
variables. As I understand it, if the range variable holds more
than 1 value then I should test using If IsArray, but I can't
get that to work, either.
At the point where the following sequence will run, the
variables have the following values:
HideCols = True (Dimmed as Boolean)
Global_ExclColRng has the value 'Nothing'
Wkbk_ExclColRng has the column 'D'
Sheet_ExclColRng has the value 'Nothing'
All 3 variables are dimmed as Range
If HideCols = True Then
On Error Resume Next
If Not IsEmpty(Global_ExclColRng) Then
Global_ExclColRng.EntireColumn.Hidden = True
ElseIf Not IsEmpty(Wkbk_ExclColRng) Then
Wkbk_ExclColRng.EntireColumn.Hidden = True
ElseIf Not IsEmpty(Sheet_ExclColRng) Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
On Error GoTo 0
End If
How do I get this to work, please?
Regards.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003