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

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

Becoming clearer. Thanks for the help.

Regards.

"Tom Ogilvy" wrote in message
...
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






---
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

I may not have understood correctly.

I now have.....

'Set user's HideCols options
If HideCols = True Then
'On Error Resume Next
If Not Global_ExclColRng Is Nothing Then
Global_ExclColRng.EntireColumn.Hidden = True
ElseIf Not Wkbk_ExclColRng Is Nothing Then
With ActiveSheet
Wkbk_ExclColRng.EntireColumn.Hidden = True
End With
ElseIf Not Sheet_ExclColRng Is Nothing Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
'' On Error GoTo 0
End If

In the above I'm setting up a test for the Wkbk_ExclColRng
option, but when the .Hidden line executes I receive the
following:

Unable to set the Hidden property of the Range class

I first thought the sheet was protected....not so.

Can you help further please?

Regards.

"Tom Ogilvy" wrote in message
...
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






---
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

Syntactically, it should go

This tests all the possibilities and it worked fine for me.

Sub Stuart()
Dim Global_ExclcolRng As Range
Dim Wkbk_ExclColrng As Range
Dim Sheet_ExclColRng As Range
Dim HideCols As Boolean
HideCols = True
Dim i As Long
For i = 0 To 3
Set Global_ExclcolRng = Nothing
Set Wkbk_ExclColrng = Nothing
Set Sheet_ExclColRng = Nothing
If i = 1 Then _
Set Wkbk_ExclColrng = Worksheets("Sheet1").Range("C1,E5,G2:H20")
If i = 2 Then _
Set Global_ExclcolRng = Worksheets("Sheet2").Range("C1,E5,G2:H20")
If i = 3 Then _
Set Sheet_ExclColRng = Worksheets("Sheet3").Range("C1,E5,G2:H20")

If HideCols = True Then
'On Error Resume Next
If Not Global_ExclcolRng Is Nothing Then
Global_ExclcolRng.EntireColumn.Hidden = True
ElseIf Not Wkbk_ExclColrng Is Nothing Then
Wkbk_ExclColrng.EntireColumn.Hidden = True
ElseIf Not Sheet_ExclColRng Is Nothing Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
'' On Error GoTo 0
End If
Next
End Sub




It must have something to do with your sheets.

Do you have merged cells?

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
I may not have understood correctly.

I now have.....

'Set user's HideCols options
If HideCols = True Then
'On Error Resume Next
If Not Global_ExclColRng Is Nothing Then
Global_ExclColRng.EntireColumn.Hidden = True
ElseIf Not Wkbk_ExclColRng Is Nothing Then
With ActiveSheet
Wkbk_ExclColRng.EntireColumn.Hidden = True
End With
ElseIf Not Sheet_ExclColRng Is Nothing Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
'' On Error GoTo 0
End If

In the above I'm setting up a test for the Wkbk_ExclColRng
option, but when the .Hidden line executes I receive the
following:

Unable to set the Hidden property of the Range class

I first thought the sheet was protected....not so.

Can you help further please?

Regards.

"Tom Ogilvy" wrote in message
...
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






---
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

Yes it works for me on a new book.
No merged cells.....that I know of.
Have tried 'my' code with alternative books
and get the same results.

Will post tomorrow if I cannot reconcile.

Regards, and again, thanks.


"Tom Ogilvy" wrote in message
...
Syntactically, it should go

This tests all the possibilities and it worked fine for me.

Sub Stuart()
Dim Global_ExclcolRng As Range
Dim Wkbk_ExclColrng As Range
Dim Sheet_ExclColRng As Range
Dim HideCols As Boolean
HideCols = True
Dim i As Long
For i = 0 To 3
Set Global_ExclcolRng = Nothing
Set Wkbk_ExclColrng = Nothing
Set Sheet_ExclColRng = Nothing
If i = 1 Then _
Set Wkbk_ExclColrng = Worksheets("Sheet1").Range("C1,E5,G2:H20")
If i = 2 Then _
Set Global_ExclcolRng = Worksheets("Sheet2").Range("C1,E5,G2:H20")
If i = 3 Then _
Set Sheet_ExclColRng = Worksheets("Sheet3").Range("C1,E5,G2:H20")

If HideCols = True Then
'On Error Resume Next
If Not Global_ExclcolRng Is Nothing Then
Global_ExclcolRng.EntireColumn.Hidden = True
ElseIf Not Wkbk_ExclColrng Is Nothing Then
Wkbk_ExclColrng.EntireColumn.Hidden = True
ElseIf Not Sheet_ExclColRng Is Nothing Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
'' On Error GoTo 0
End If
Next
End Sub




It must have something to do with your sheets.

Do you have merged cells?

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
I may not have understood correctly.

I now have.....

'Set user's HideCols options
If HideCols = True Then
'On Error Resume Next
If Not Global_ExclColRng Is Nothing Then
Global_ExclColRng.EntireColumn.Hidden = True
ElseIf Not Wkbk_ExclColRng Is Nothing Then
With ActiveSheet
Wkbk_ExclColRng.EntireColumn.Hidden = True
End With
ElseIf Not Sheet_ExclColRng Is Nothing Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
'' On Error GoTo 0
End If

In the above I'm setting up a test for the Wkbk_ExclColRng
option, but when the .Hidden line executes I receive the
following:

Unable to set the Hidden property of the Range class

I first thought the sheet was protected....not so.

Can you help further please?

Regards.

"Tom Ogilvy" wrote in message
...
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






---
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






---
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

I've tested the sequence:
If HideCols = True Then
'On Error Resume Next
If Not Global_ExclcolRng Is Nothing Then
Global_ExclcolRng.EntireColumn.Hidden = True
ElseIf Not Wkbk_ExclColrng Is Nothing Then
Wkbk_ExclColrng.EntireColumn.Hidden = True
ElseIf Not Sheet_ExclColRng Is Nothing Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
'' On Error GoTo 0
End If

It works on new empty workbooks, but fails on nearly all
my 'test' workbooks, with the message 'Unable to set the
Hidden property of the Range class'

If it's something in the sheets, then any pointers as to what
to look for, please?

Have noticed the following: normally When I click the Reset
button after running code to a Breakpoint, the VBE windows
cascade and leave me in an Event Class module. This is now
not always the case....sometimes I'm left in the module with
the break point.

There is also a Userform problem, but I will post that as a
separate issue.

Regards and thanks.



"Tom Ogilvy" wrote in message
...
Syntactically, it should go

This tests all the possibilities and it worked fine for me.

Sub Stuart()
Dim Global_ExclcolRng As Range
Dim Wkbk_ExclColrng As Range
Dim Sheet_ExclColRng As Range
Dim HideCols As Boolean
HideCols = True
Dim i As Long
For i = 0 To 3
Set Global_ExclcolRng = Nothing
Set Wkbk_ExclColrng = Nothing
Set Sheet_ExclColRng = Nothing
If i = 1 Then _
Set Wkbk_ExclColrng = Worksheets("Sheet1").Range("C1,E5,G2:H20")
If i = 2 Then _
Set Global_ExclcolRng = Worksheets("Sheet2").Range("C1,E5,G2:H20")
If i = 3 Then _
Set Sheet_ExclColRng = Worksheets("Sheet3").Range("C1,E5,G2:H20")

If HideCols = True Then
'On Error Resume Next
If Not Global_ExclcolRng Is Nothing Then
Global_ExclcolRng.EntireColumn.Hidden = True
ElseIf Not Wkbk_ExclColrng Is Nothing Then
Wkbk_ExclColrng.EntireColumn.Hidden = True
ElseIf Not Sheet_ExclColRng Is Nothing Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
'' On Error GoTo 0
End If
Next
End Sub




It must have something to do with your sheets.

Do you have merged cells?

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
I may not have understood correctly.

I now have.....

'Set user's HideCols options
If HideCols = True Then
'On Error Resume Next
If Not Global_ExclColRng Is Nothing Then
Global_ExclColRng.EntireColumn.Hidden = True
ElseIf Not Wkbk_ExclColRng Is Nothing Then
With ActiveSheet
Wkbk_ExclColRng.EntireColumn.Hidden = True
End With
ElseIf Not Sheet_ExclColRng Is Nothing Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
'' On Error GoTo 0
End If

In the above I'm setting up a test for the Wkbk_ExclColRng
option, but when the .Hidden line executes I receive the
following:

Unable to set the Hidden property of the Range class

I first thought the sheet was protected....not so.

Can you help further please?

Regards.

"Tom Ogilvy" wrote in message
...
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






---
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






---
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

If you don't have merged cells and the worksheet isn't protected, I can't
think of anything that would cause that error.

make one of the troublesome sheets active. Go to the immediate window in
the VBE and do

Range("B9,D12").EntireColumn.Hidden = True <CR

use a range that would be typical for one of your variables.

Another possibility, throw in

ActiveCell.Activate
If HideCols = True Then


Regards,
Tom Ogilvy

"Stuart" wrote in message
...
I've tested the sequence:
If HideCols = True Then
'On Error Resume Next
If Not Global_ExclcolRng Is Nothing Then
Global_ExclcolRng.EntireColumn.Hidden = True
ElseIf Not Wkbk_ExclColrng Is Nothing Then
Wkbk_ExclColrng.EntireColumn.Hidden = True
ElseIf Not Sheet_ExclColRng Is Nothing Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
'' On Error GoTo 0
End If

It works on new empty workbooks, but fails on nearly all
my 'test' workbooks, with the message 'Unable to set the
Hidden property of the Range class'

If it's something in the sheets, then any pointers as to what
to look for, please?

Have noticed the following: normally When I click the Reset
button after running code to a Breakpoint, the VBE windows
cascade and leave me in an Event Class module. This is now
not always the case....sometimes I'm left in the module with
the break point.

There is also a Userform problem, but I will post that as a
separate issue.

Regards and thanks.



"Tom Ogilvy" wrote in message
...
Syntactically, it should go

This tests all the possibilities and it worked fine for me.

Sub Stuart()
Dim Global_ExclcolRng As Range
Dim Wkbk_ExclColrng As Range
Dim Sheet_ExclColRng As Range
Dim HideCols As Boolean
HideCols = True
Dim i As Long
For i = 0 To 3
Set Global_ExclcolRng = Nothing
Set Wkbk_ExclColrng = Nothing
Set Sheet_ExclColRng = Nothing
If i = 1 Then _
Set Wkbk_ExclColrng = Worksheets("Sheet1").Range("C1,E5,G2:H20")
If i = 2 Then _
Set Global_ExclcolRng = Worksheets("Sheet2").Range("C1,E5,G2:H20")
If i = 3 Then _
Set Sheet_ExclColRng = Worksheets("Sheet3").Range("C1,E5,G2:H20")

If HideCols = True Then
'On Error Resume Next
If Not Global_ExclcolRng Is Nothing Then
Global_ExclcolRng.EntireColumn.Hidden = True
ElseIf Not Wkbk_ExclColrng Is Nothing Then
Wkbk_ExclColrng.EntireColumn.Hidden = True
ElseIf Not Sheet_ExclColRng Is Nothing Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
'' On Error GoTo 0
End If
Next
End Sub




It must have something to do with your sheets.

Do you have merged cells?

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
I may not have understood correctly.

I now have.....

'Set user's HideCols options
If HideCols = True Then
'On Error Resume Next
If Not Global_ExclColRng Is Nothing Then
Global_ExclColRng.EntireColumn.Hidden = True
ElseIf Not Wkbk_ExclColRng Is Nothing Then
With ActiveSheet
Wkbk_ExclColRng.EntireColumn.Hidden = True
End With
ElseIf Not Sheet_ExclColRng Is Nothing Then
Sheet_ExclColRng.EntireColumn.Hidden = True
End If
'' On Error GoTo 0
End If

In the above I'm setting up a test for the Wkbk_ExclColRng
option, but when the .Hidden line executes I receive the
following:

Unable to set the Hidden property of the Range class

I first thought the sheet was protected....not so.

Can you help further please?

Regards.

"Tom Ogilvy" wrote in message
...
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






---
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






---
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