setting range().hidden=True causes range error 1004

[STEVE BELL]

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

[STEVE BELL]

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" <[email protected]> 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
>

[Tom Ogilvy]

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" <[email protected]> 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
>
>

[Tom Ogilvy]

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" <[email protected]> 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" <[email protected]> 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
> >
>
>

[STEVE BELL]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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
>>
>>
>
>

[Tom Ogilvy]

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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> 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
> >>
> >>
> >
> >
>
>

[STEVE BELL]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>