Hiding/Unhiding Columns

I want to hide or unhide columns if the value in the bottom row of the column
is zero. This may change when the number in another cell is changed. Is
this possible?

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Columns(8).Hidden = Cells(Rows.Count, 8).End(xlUp).Value = 0
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> wrote in message
news:89490DF3-1B4E-444A-99F1-EB2DABE415DD@microsoft.com...
> I want to hide or unhide columns if the value in the bottom row of the
column
> is zero. This may change when the number in another cell is changed. Is
> this possible?
>
>

Can you tell me what the significance of the (8) is, so I can understand what
the code is doing?

Columns(8) is column H
You could also type: Columns("H") for better readability (but poor
performance)

HTH
--
AP

"xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> a écrit dans le message
de news: 627CD2DA-0F17-47F5-B682-D5A53D43FAD2@microsoft.com...
> Can you tell me what the significance of the (8) is, so I can understand
> what
> the code is doing?

I'm not getting this to work. I want to hide any columns in the range of F
to S if the value in row 38 of the column is zero. This can change from zero
when the value in cell B5 is changed

"Ardus Petus" wrote:

> Columns(8) is column H
> You could also type: Columns("H") for better readability (but poor
> performance)
>
> HTH
> --
> AP
>
> "xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> a écrit dans le message
> de news: 627CD2DA-0F17-47F5-B682-D5A53D43FAD2@microsoft.com...
> > Can you tell me what the significance of the (8) is, so I can understand
> > what
> > the code is doing?
>
>
>

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lCol As Long
If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub
For lCol = Columns("F").Column To Columns("S").Column
With Cells(38, lCol)
.EntireColumn.Hidden = (.Value = 0)
End With
Next lCol
End Sub

HTH
--
AP

"xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> a écrit dans le message
de news: E2EC27C1-E55F-41B0-8920-2A150822F8F2@microsoft.com...
> I'm not getting this to work. I want to hide any columns in the range of
> F
> to S if the value in row 38 of the column is zero. This can change from
> zero
> when the value in cell B5 is changed
>
> "Ardus Petus" wrote:
>
>> Columns(8) is column H
>> You could also type: Columns("H") for better readability (but poor
>> performance)
>>
>> HTH
>> --
>> AP
>>
>> "xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> a écrit dans le
>> message
>> de news: 627CD2DA-0F17-47F5-B682-D5A53D43FAD2@microsoft.com...
>> > Can you tell me what the significance of the (8) is, so I can
>> > understand
>> > what
>> > the code is doing?
>>
>>
>>

Brilliant, works perfectly, thank you very much!

"Ardus Petus" wrote:

> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim lCol As Long
> If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub
> For lCol = Columns("F").Column To Columns("S").Column
> With Cells(38, lCol)
> .EntireColumn.Hidden = (.Value = 0)
> End With
> Next lCol
> End Sub
>
> HTH
> --
> AP
>
> "xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> a écrit dans le message
> de news: E2EC27C1-E55F-41B0-8920-2A150822F8F2@microsoft.com...
> > I'm not getting this to work. I want to hide any columns in the range of
> > F
> > to S if the value in row 38 of the column is zero. This can change from
> > zero
> > when the value in cell B5 is changed
> >
> > "Ardus Petus" wrote:
> >
> >> Columns(8) is column H
> >> You could also type: Columns("H") for better readability (but poor
> >> performance)
> >>
> >> HTH
> >> --
> >> AP
> >>
> >> "xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> a écrit dans le
> >> message
> >> de news: 627CD2DA-0F17-47F5-B682-D5A53D43FAD2@microsoft.com...
> >> > Can you tell me what the significance of the (8) is, so I can
> >> > understand
> >> > what
> >> > the code is doing?
> >>
> >>
> >>
>
>
>

It helps if you give full details at the beginning.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> wrote in message
news:B00B6989-E4DA-4387-9BBD-FAA134D12258@microsoft.com...
> Brilliant, works perfectly, thank you very much!
>
> "Ardus Petus" wrote:
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim lCol As Long
> > If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub
> > For lCol = Columns("F").Column To Columns("S").Column
> > With Cells(38, lCol)
> > .EntireColumn.Hidden = (.Value = 0)
> > End With
> > Next lCol
> > End Sub
> >
> > HTH
> > --
> > AP
> >
> > "xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> a écrit dans le
message
> > de news: E2EC27C1-E55F-41B0-8920-2A150822F8F2@microsoft.com...
> > > I'm not getting this to work. I want to hide any columns in the
range of
> > > F
> > > to S if the value in row 38 of the column is zero. This can change
from
> > > zero
> > > when the value in cell B5 is changed
> > >
> > > "Ardus Petus" wrote:
> > >
> > >> Columns(8) is column H
> > >> You could also type: Columns("H") for better readability (but poor
> > >> performance)
> > >>
> > >> HTH
> > >> --
> > >> AP
> > >>
> > >> "xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> a écrit dans le
> > >> message
> > >> de news: 627CD2DA-0F17-47F5-B682-D5A53D43FAD2@microsoft.com...
> > >> > Can you tell me what the significance of the (8) is, so I can
> > >> > understand
> > >> > what
> > >> > the code is doing?
> > >>
> > >>
> > >>
> >
> >
> >

I appreciate that now, I'll know that in future.

Thanks again for your help.

Regards

Karen

"Bob Phillips" wrote:

> It helps if you give full details at the beginning.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> wrote in message
> news:B00B6989-E4DA-4387-9BBD-FAA134D12258@microsoft.com...
> > Brilliant, works perfectly, thank you very much!
> >
> > "Ardus Petus" wrote:
> >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim lCol As Long
> > > If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub
> > > For lCol = Columns("F").Column To Columns("S").Column
> > > With Cells(38, lCol)
> > > .EntireColumn.Hidden = (.Value = 0)
> > > End With
> > > Next lCol
> > > End Sub
> > >
> > > HTH
> > > --
> > > AP
> > >
> > > "xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> a écrit dans le
> message
> > > de news: E2EC27C1-E55F-41B0-8920-2A150822F8F2@microsoft.com...
> > > > I'm not getting this to work. I want to hide any columns in the
> range of
> > > > F
> > > > to S if the value in row 38 of the column is zero. This can change
> from
> > > > zero
> > > > when the value in cell B5 is changed
> > > >
> > > > "Ardus Petus" wrote:
> > > >
> > > >> Columns(8) is column H
> > > >> You could also type: Columns("H") for better readability (but poor
> > > >> performance)
> > > >>
> > > >> HTH
> > > >> --
> > > >> AP
> > > >>
> > > >> "xkarenxxxx" <xkarenxxxx@discussions.microsoft.com> a écrit dans le
> > > >> message
> > > >> de news: 627CD2DA-0F17-47F5-B682-D5A53D43FAD2@microsoft.com...
> > > >> > Can you tell me what the significance of the (8) is, so I can
> > > >> > understand
> > > >> > what
> > > >> > the code is doing?
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>
>