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