|
setting range().hidden=True causes range error 1004
First - thanks for any help.
Using XL 2000 on Win XP I am using the following lines of code in 2 places in my workbook to show and hide columns: ThisWorkbook.Sheets(WSn).Range(Columns(1), Columns(col2 + 1)).Hidden = False ThisWorkbook.Sheets(WSn).Range(Columns(2), Columns(col1)).Hidden = True <<<< error 1004 In another module where I use the actual sheet name - it works fine. ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(x)).Hidden = False 'some code ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(col1)).Hidden = True But in this module where I am using a string variable for the sheet name the second line errors out. (1004) I even copied the working code over and changed the sheet name to the variable. Double checked the variable values and they appear to be reasonable Can any body try to explain this to me...? Thanks... -- steveB Remove "AYN" from email to respond |
setting range().hidden=True causes range error 1004
Tried altering the code (trying to locate the problem:
For x = 2 To 256 Dim y, z y = Cells(2, x) z = Cells(2, x + 1) If Cells(2, x) = 0 And Cells(2, x + 1) = 0 Then Columns(x).Hidden = True ElseIf Cells(2, x) 0 Then Exit For End If Next This worked until x = 15 Checked column 15 and couldn't find anything different between it and the previous columns. Now I am really stumped... -- steveB Remove "AYN" from email to respond "STEVE BELL" wrote in message news:FLIRe.44730$yv2.31525@trnddc04... First - thanks for any help. Using XL 2000 on Win XP I am using the following lines of code in 2 places in my workbook to show and hide columns: ThisWorkbook.Sheets(WSn).Range(Columns(1), Columns(col2 + 1)).Hidden = False ThisWorkbook.Sheets(WSn).Range(Columns(2), Columns(col1)).Hidden = True <<<< error 1004 In another module where I use the actual sheet name - it works fine. ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(x)).Hidden = False 'some code ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(col1)).Hidden = True But in this module where I am using a string variable for the sheet name the second line errors out. (1004) I even copied the working code over and changed the sheet name to the variable. Double checked the variable values and they appear to be reasonable Can any body try to explain this to me...? Thanks... -- steveB Remove "AYN" from email to respond |
setting range().hidden=True causes range error 1004
With ThisWorkbook.Sheets(WSn) _
.Range(.Columns(1), .Columns(col2 + 1)).Hidden = False .Range(.Columns(2), .Columns(col1)).Hidden = True End With Assuming Col2 has a value between 1 and 255 Col1 has a value between 1 and 256 No merged cells. -- Regards, Tom Ogilvy ThisWorkbook.Sheets(WSn).Range(Columns(2), Columns(col1)).Hidden = True "STEVE BELL" wrote in message news:FLIRe.44730$yv2.31525@trnddc04... First - thanks for any help. Using XL 2000 on Win XP I am using the following lines of code in 2 places in my workbook to show and hide columns: ThisWorkbook.Sheets(WSn).Range(Columns(1), Columns(col2 + 1)).Hidden = False ThisWorkbook.Sheets(WSn).Range(Columns(2), Columns(col1)).Hidden = True <<<< error 1004 In another module where I use the actual sheet name - it works fine. ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(x)).Hidden = False 'some code ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(col1)).Hidden = True But in this module where I am using a string variable for the sheet name the second line errors out. (1004) I even copied the working code over and changed the sheet name to the variable. Double checked the variable values and they appear to be reasonable Can any body try to explain this to me...? Thanks... -- steveB Remove "AYN" from email to respond |
setting range().hidden=True causes range error 1004
Sub AA()
For x = 2 To 255 Dim y, z y = Cells(2, x) z = Cells(2, x + 1) If Cells(2, x) = 0 And Cells(2, x + 1) = 0 Then Columns(x).Hidden = True ElseIf Cells(2, x) 0 Then Exit For End If Next End Sub worked fine for me. Hide columns B:IU. Obviously you can't do 256 + 1, so I changed the loop to 255. -- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:K3JRe.12360$qg2.8534@trnddc05... Tried altering the code (trying to locate the problem: For x = 2 To 256 Dim y, z y = Cells(2, x) z = Cells(2, x + 1) If Cells(2, x) = 0 And Cells(2, x + 1) = 0 Then Columns(x).Hidden = True ElseIf Cells(2, x) 0 Then Exit For End If Next This worked until x = 15 Checked column 15 and couldn't find anything different between it and the previous columns. Now I am really stumped... -- steveB Remove "AYN" from email to respond "STEVE BELL" wrote in message news:FLIRe.44730$yv2.31525@trnddc04... First - thanks for any help. Using XL 2000 on Win XP I am using the following lines of code in 2 places in my workbook to show and hide columns: ThisWorkbook.Sheets(WSn).Range(Columns(1), Columns(col2 + 1)).Hidden = False ThisWorkbook.Sheets(WSn).Range(Columns(2), Columns(col1)).Hidden = True <<<< error 1004 In another module where I use the actual sheet name - it works fine. ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(x)).Hidden = False 'some code ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(col1)).Hidden = True But in this module where I am using a string variable for the sheet name the second line errors out. (1004) I even copied the working code over and changed the sheet name to the variable. Double checked the variable values and they appear to be reasonable Can any body try to explain this to me...? Thanks... -- steveB Remove "AYN" from email to respond |
setting range().hidden=True causes range error 1004
Tom,
As always - you came through... Had to remove the line continuation ( _ ) and the . from .columns my system didn't like either of those. Now it works! Tried you For ... Next loop but didn't need it after your With ... End With still don't understand why my code worked in one module, but not in the other.... Thanks for getting me out of a mind-twister!!! -- steveB Remove "AYN" from email to respond "Tom Ogilvy" wrote in message ... With ThisWorkbook.Sheets(WSn) _ .Range(.Columns(1), .Columns(col2 + 1)).Hidden = False .Range(.Columns(2), .Columns(col1)).Hidden = True End With Assuming Col2 has a value between 1 and 255 Col1 has a value between 1 and 256 No merged cells. -- Regards, Tom Ogilvy ThisWorkbook.Sheets(WSn).Range(Columns(2), Columns(col1)).Hidden = True "STEVE BELL" wrote in message news:FLIRe.44730$yv2.31525@trnddc04... First - thanks for any help. Using XL 2000 on Win XP I am using the following lines of code in 2 places in my workbook to show and hide columns: ThisWorkbook.Sheets(WSn).Range(Columns(1), Columns(col2 + 1)).Hidden = False ThisWorkbook.Sheets(WSn).Range(Columns(2), Columns(col1)).Hidden = True <<<< error 1004 In another module where I use the actual sheet name - it works fine. ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(x)).Hidden = False 'some code ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(col1)).Hidden = True But in this module where I am using a string variable for the sheet name the second line errors out. (1004) I even copied the working code over and changed the sheet name to the variable. Double checked the variable values and they appear to be reasonable Can any body try to explain this to me...? Thanks... -- steveB Remove "AYN" from email to respond |
setting range().hidden=True causes range error 1004
Yes, the line continuation character should have been removed (reminent of
some editing). But the periods are essential if you want it to work in all instances. Those were the whole point. -- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:f%JRe.28122$FL1.27749@trnddc09... Tom, As always - you came through... Had to remove the line continuation ( _ ) and the . from .columns my system didn't like either of those. Now it works! Tried you For ... Next loop but didn't need it after your With ... End With still don't understand why my code worked in one module, but not in the other.... Thanks for getting me out of a mind-twister!!! -- steveB Remove "AYN" from email to respond "Tom Ogilvy" wrote in message ... With ThisWorkbook.Sheets(WSn) _ .Range(.Columns(1), .Columns(col2 + 1)).Hidden = False .Range(.Columns(2), .Columns(col1)).Hidden = True End With Assuming Col2 has a value between 1 and 255 Col1 has a value between 1 and 256 No merged cells. -- Regards, Tom Ogilvy ThisWorkbook.Sheets(WSn).Range(Columns(2), Columns(col1)).Hidden = True "STEVE BELL" wrote in message news:FLIRe.44730$yv2.31525@trnddc04... First - thanks for any help. Using XL 2000 on Win XP I am using the following lines of code in 2 places in my workbook to show and hide columns: ThisWorkbook.Sheets(WSn).Range(Columns(1), Columns(col2 + 1)).Hidden = False ThisWorkbook.Sheets(WSn).Range(Columns(2), Columns(col1)).Hidden = True <<<< error 1004 In another module where I use the actual sheet name - it works fine. ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(x)).Hidden = False 'some code ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(col1)).Hidden = True But in this module where I am using a string variable for the sheet name the second line errors out. (1004) I even copied the working code over and changed the sheet name to the variable. Double checked the variable values and they appear to be reasonable Can any body try to explain this to me...? Thanks... -- steveB Remove "AYN" from email to respond |
setting range().hidden=True causes range error 1004
Tom,
My computer (xl2000, wxp) gave me an error when I left the "."'s in. Got rid of them and it started working... I'm starting to believe that my machine has picked something up... Spent the better part of the day trying to clean it up and have seen some improvement... Thanks... you are always there!!! -- steveB Remove "AYN" from email to respond "Tom Ogilvy" wrote in message ... Yes, the line continuation character should have been removed (reminent of some editing). But the periods are essential if you want it to work in all instances. Those were the whole point. -- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:f%JRe.28122$FL1.27749@trnddc09... Tom, As always - you came through... Had to remove the line continuation ( _ ) and the . from .columns my system didn't like either of those. Now it works! Tried you For ... Next loop but didn't need it after your With ... End With still don't understand why my code worked in one module, but not in the other.... Thanks for getting me out of a mind-twister!!! -- steveB Remove "AYN" from email to respond "Tom Ogilvy" wrote in message ... With ThisWorkbook.Sheets(WSn) _ .Range(.Columns(1), .Columns(col2 + 1)).Hidden = False .Range(.Columns(2), .Columns(col1)).Hidden = True End With Assuming Col2 has a value between 1 and 255 Col1 has a value between 1 and 256 No merged cells. -- Regards, Tom Ogilvy ThisWorkbook.Sheets(WSn).Range(Columns(2), Columns(col1)).Hidden = True "STEVE BELL" wrote in message news:FLIRe.44730$yv2.31525@trnddc04... First - thanks for any help. Using XL 2000 on Win XP I am using the following lines of code in 2 places in my workbook to show and hide columns: ThisWorkbook.Sheets(WSn).Range(Columns(1), Columns(col2 + 1)).Hidden = False ThisWorkbook.Sheets(WSn).Range(Columns(2), Columns(col1)).Hidden = True <<<< error 1004 In another module where I use the actual sheet name - it works fine. ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(x)).Hidden = False 'some code ThisWorkbook.Sheets("Summary").Range(Columns(2), Columns(col1)).Hidden = True But in this module where I am using a string variable for the sheet name the second line errors out. (1004) I even copied the working code over and changed the sheet name to the variable. Double checked the variable values and they appear to be reasonable Can any body try to explain this to me...? Thanks... -- steveB Remove "AYN" from email to respond |
| All times are GMT +1. The time now is 03:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com