Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
Have you tried Rob Bovey's code cleaner? Might be worth a try.
http://www.appspro.com -- Regards, Tom Ogilvy "Stuart" wrote in message ... Not too sure if ActiveCell.Activate makes a difference, but can confirm that using Range("D12").EntireColumn.Hidden = True in the immediate window will successfully hide the range. I put a break on "If HideCols = True Then" and then typed into the Immediate window. It hid the range. I cannot seem to find one workbook/sheet that will either consistently fail, or consistently work. I'm beginning to wonder if there may be a situation where it may be a lucky choice of workbook/sheet (first time through) but that after that, then it will fail....whatever. Should I consider going to a subroutine for the code that sets the 'Exclude' ranges ....might that help? Regards and thanks. "Tom Ogilvy" wrote in message ... 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 --- 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
Thanks for all your help.
Will pursue Regards, "Tom Ogilvy" wrote in message ... Can't tell you Stuart. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Not too sure if ActiveCell.Activate makes a difference, but can confirm that using Range("D12").EntireColumn.Hidden = True in the immediate window will successfully hide the range. I put a break on "If HideCols = True Then" and then typed into the Immediate window. It hid the range. I cannot seem to find one workbook/sheet that will either consistently fail, or consistently work. I'm beginning to wonder if there may be a situation where it may be a lucky choice of workbook/sheet (first time through) but that after that, then it will fail....whatever. Should I consider going to a subroutine for the code that sets the 'Exclude' ranges ....might that help? Regards and thanks. "Tom Ogilvy" wrote in message ... 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 --- 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
One more thought.
give: 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 what do you expect this to do. If Global_ExclcolRng = Range("A1,C1") and Wkbk_ExclColrng = Range("B1:D1") and Sheet_ExclColRng = Nothing what would columns would you expect to have hidden. If you say columns A,B,C,D you would be wrong. Only column A and C would be hidden - is that what you intended? -- Regards, Tom Ogilvy Stuart wrote in message ... Thanks for all your help. Will pursue Regards, "Tom Ogilvy" wrote in message ... Can't tell you Stuart. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Not too sure if ActiveCell.Activate makes a difference, but can confirm that using Range("D12").EntireColumn.Hidden = True in the immediate window will successfully hide the range. I put a break on "If HideCols = True Then" and then typed into the Immediate window. It hid the range. I cannot seem to find one workbook/sheet that will either consistently fail, or consistently work. I'm beginning to wonder if there may be a situation where it may be a lucky choice of workbook/sheet (first time through) but that after that, then it will fail....whatever. Should I consider going to a subroutine for the code that sets the 'Exclude' ranges ....might that help? Regards and thanks. "Tom Ogilvy" wrote in message ... 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 --- 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
If Global_ExclColRng
is Dim Global_ExclColRng as Range then this variable refers to a specific range on a specific worksheet in a specific workbook. It doesn't apply to the activesheet unless that is where it was set. If sounds like if you are looping through sheets or even workbooks you want for each sh in Worksheets ' just representing a loop If HideCols = True Then 'On Error Resume Next If Not Global_ExclcolRng Is Nothing Then sh.Range(Global_ExclcolRng.address). _ EntireColumn.Hidden = True this uses the address of the chosen cells, but works on the intended sheet rather than the original (to which Global_ExcelcolRng actually refers to). I suspect if the sheet where it was set was protected, you thought you were working on another sheet, but the code was trying to hide columns on the original sheet (which was protected). When you unprotect the original sheet, then you don't get the error because each time the columns on the original sheet are being hidden (if you look at the intended sheet, the columns would still be visible). Since you didn't get an error, you were probably happy and didn't notice the columns intended didn't get hidden. That would be my guess. -- Regards, Tom Ogilvy Stuart wrote in message ... I would have expected cols A and C to be hidden. However, the code 'might' now be working!!! Let me run this by you...... The initial sequences in the routine let the user choose which workbooks to print, and builds a stringarray of the names. I then open the first workbook and set Global_ExclColRng (if the user wants to hide the same cols in every book to be printed) or Wkbk_ExclColRng (if user wants to hide the same cols in each sheet of that workbook) or Sheet_ExclColRng (if user wants to control the hiding of cols at sheet level) I'm basically trying to get user's options globally across all books and sheets, or at workbook level or sheet level. So the If sequence is constructed to expect one (at most) option to be true. Now the point is that user will set the variable with the 1st book active, and this is before the For Each ws In ActiveWorkbook.Worksheets sequence....where the sheets are UNPROTECTED. I changed the code to Unprotect the ActiveSheet in that first workbook, just before any of the ExclColRng variables are set, and since then it appears to work. I had noticed in the Locals window that after the Global_ExclCol variable had been set, if you expanded Global_ExclColRng, then some of the other elements had that same message (unable to set the hidden property of the range Class) and that set me thinking about the protected/unprotected state of that sheet where these variables were being set. If this logic is correct, then apologies for insisting that sheets were unprotected...they were. What I appear not to have known might be that when the variable is set, so must be the sheet against which it is set. Probably way off, but... Regards. "Tom Ogilvy" wrote in message ... One more thought. give: 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 what do you expect this to do. If Global_ExclcolRng = Range("A1,C1") and Wkbk_ExclColrng = Range("B1:D1") and Sheet_ExclColRng = Nothing what would columns would you expect to have hidden. If you say columns A,B,C,D you would be wrong. Only column A and C would be hidden - is that what you intended? -- Regards, Tom Ogilvy Stuart wrote in message ... Thanks for all your help. Will pursue Regards, "Tom Ogilvy" wrote in message ... Can't tell you Stuart. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Not too sure if ActiveCell.Activate makes a difference, but can confirm that using Range("D12").EntireColumn.Hidden = True in the immediate window will successfully hide the range. I put a break on "If HideCols = True Then" and then typed into the Immediate window. It hid the range. I cannot seem to find one workbook/sheet that will either consistently fail, or consistently work. I'm beginning to wonder if there may be a situation where it may be a lucky choice of workbook/sheet (first time through) but that after that, then it will fail....whatever. Should I consider going to a subroutine for the code that sets the 'Exclude' ranges ....might that help? Regards and thanks. "Tom Ogilvy" wrote in message ... 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 --- 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsEmpty and Is Nothinq problems
You were absolutely correct,
sh.Range(Global_ExclcolRng.address). _ EntireColumn.Hidden = True was the key. I hadn't noticed the failure in the other sheets. Thanks for all your help. Regards. "Tom Ogilvy" wrote in message ... If Global_ExclColRng is Dim Global_ExclColRng as Range then this variable refers to a specific range on a specific worksheet in a specific workbook. It doesn't apply to the activesheet unless that is where it was set. If sounds like if you are looping through sheets or even workbooks you want for each sh in Worksheets ' just representing a loop If HideCols = True Then 'On Error Resume Next If Not Global_ExclcolRng Is Nothing Then sh.Range(Global_ExclcolRng.address). _ EntireColumn.Hidden = True this uses the address of the chosen cells, but works on the intended sheet rather than the original (to which Global_ExcelcolRng actually refers to). I suspect if the sheet where it was set was protected, you thought you were working on another sheet, but the code was trying to hide columns on the original sheet (which was protected). When you unprotect the original sheet, then you don't get the error because each time the columns on the original sheet are being hidden (if you look at the intended sheet, the columns would still be visible). Since you didn't get an error, you were probably happy and didn't notice the columns intended didn't get hidden. That would be my guess. -- Regards, Tom Ogilvy --- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
aauugghhh...#div/o problems & various average formula problems | Excel Worksheet Functions | |||
Loop Statement through If Not IsEmpty Then Paste into Destination | Excel Discussion (Misc queries) | |||
Way around CUT problems... | Excel Discussion (Misc queries) | |||
Form If IsEmpty Syntax | Excel Discussion (Misc queries) | |||
SUM Problems | Excel Worksheet Functions |