Hiding rows

[Davidjc52]

We would like to be able to enter a value in a cell and if it meets our
predefined criteria the entire row would automatically hide. Is this possible
in excel?

Thanks

[Chip Pearson]

You can use and event procedure to do this (see
http://www.cpearson.com/excel/events.htm for more info about
events). Right click the sheet tab and choose View Code. In the
VBA code module that appears, paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then 'change cell as desired
If Target.Value > 10 Then ' change criteria as desired
Target.EntireRow.Hidden = True
Else
Target.EntireRow.Hidden = False
End If
End If

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Davidjc52" <Davidjc52@discussions.microsoft.com> wrote in
message
news:F0E8927F-5563-4B80-AD01-04076625CCDB@microsoft.com...
> We would like to be able to enter a value in a cell and if it
> meets our
> predefined criteria the entire row would automatically hide. Is
> this possible
> in excel?
>
> Thanks

[Davidjc52]

Thank you Chip

"Chip Pearson" wrote:

> You can use and event procedure to do this (see
> http://www.cpearson.com/excel/events.htm for more info about
> events). Right click the sheet tab and choose View Code. In the
> VBA code module that appears, paste the following code:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Target.Address = "$A$1" Then 'change cell as desired
> If Target.Value > 10 Then ' change criteria as desired
> Target.EntireRow.Hidden = True
> Else
> Target.EntireRow.Hidden = False
> End If
> End If
>
> End Sub
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Davidjc52" <Davidjc52@discussions.microsoft.com> wrote in
> message
> news:F0E8927F-5563-4B80-AD01-04076625CCDB@microsoft.com...
> > We would like to be able to enter a value in a cell and if it
> > meets our
> > predefined criteria the entire row would automatically hide. Is
> > this possible
> > in excel?
> >
> > Thanks
>
>
>

[Davidjc52]

Chip,

How would you set this to a range of cells in column A


"Chip Pearson" wrote:

> You can use and event procedure to do this (see
> http://www.cpearson.com/excel/events.htm for more info about
> events). Right click the sheet tab and choose View Code. In the
> VBA code module that appears, paste the following code:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Target.Address = "$A$1" Then 'change cell as desired
> If Target.Value > 10 Then ' change criteria as desired
> Target.EntireRow.Hidden = True
> Else
> Target.EntireRow.Hidden = False
> End If
> End If
>
> End Sub
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Davidjc52" <Davidjc52@discussions.microsoft.com> wrote in
> message
> news:F0E8927F-5563-4B80-AD01-04076625CCDB@microsoft.com...
> > We would like to be able to enter a value in a cell and if it
> > meets our
> > predefined criteria the entire row would automatically hide. Is
> > this possible
> > in excel?
> >
> > Thanks
>
>
>

[Chip Pearson]

David,


Change

If Target.Address = "$A$1" Then

to

If Not Application.Intersect(Target, Range("A1:A100")) Is
Nothing Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Davidjc52" <Davidjc52@discussions.microsoft.com> wrote in
message
news:2882B3D7-55BD-4D1A-8140-37B50F94B6F5@microsoft.com...
> Chip,
>
> How would you set this to a range of cells in column A
>
>
> "Chip Pearson" wrote:
>
>> You can use and event procedure to do this (see
>> http://www.cpearson.com/excel/events.htm for more info about
>> events). Right click the sheet tab and choose View Code. In
>> the
>> VBA code module that appears, paste the following code:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>
>> If Target.Address = "$A$1" Then 'change cell as desired
>> If Target.Value > 10 Then ' change criteria as desired
>> Target.EntireRow.Hidden = True
>> Else
>> Target.EntireRow.Hidden = False
>> End If
>> End If
>>
>> End Sub
>>
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "Davidjc52" <Davidjc52@discussions.microsoft.com> wrote in
>> message
>> news:F0E8927F-5563-4B80-AD01-04076625CCDB@microsoft.com...
>> > We would like to be able to enter a value in a cell and if
>> > it
>> > meets our
>> > predefined criteria the entire row would automatically hide.
>> > Is
>> > this possible
>> > in excel?
>> >
>> > Thanks
>>
>>
>>

[Dave Peterson]

One way...

Private Sub Worksheet_Change(ByVal Target As Range)
if target.cells.count > 1 then exit sub
if intersect(target,me.range("a1:a25")) is nothing then exit sub

If Target.Value > 10 Then ' change criteria as desired
Target.EntireRow.Hidden = True
Else
Target.EntireRow.Hidden = False
End If

End Sub


Davidjc52 wrote:
>
> Chip,
>
> How would you set this to a range of cells in column A
>
> "Chip Pearson" wrote:
>
> > You can use and event procedure to do this (see
> > http://www.cpearson.com/excel/events.htm for more info about
> > events). Right click the sheet tab and choose View Code. In the
> > VBA code module that appears, paste the following code:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > If Target.Address = "$A$1" Then 'change cell as desired
> > If Target.Value > 10 Then ' change criteria as desired
> > Target.EntireRow.Hidden = True
> > Else
> > Target.EntireRow.Hidden = False
> > End If
> > End If
> >
> > End Sub
> >
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> > "Davidjc52" <Davidjc52@discussions.microsoft.com> wrote in
> > message
> > news:F0E8927F-5563-4B80-AD01-04076625CCDB@microsoft.com...
> > > We would like to be able to enter a value in a cell and if it
> > > meets our
> > > predefined criteria the entire row would automatically hide. Is
> > > this possible
> > > in excel?
> > >
> > > Thanks
> >
> >
> >

--

Dave Peterson

[Davidjc52]

Thank You Chip. That was perfect. Your website is a great source of
information.

David Curlis

"Chip Pearson" wrote:

> David,
>
>
> Change
>
> If Target.Address = "$A$1" Then
>
> to
>
> If Not Application.Intersect(Target, Range("A1:A100")) Is
> Nothing Then
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Davidjc52" <Davidjc52@discussions.microsoft.com> wrote in
> message
> news:2882B3D7-55BD-4D1A-8140-37B50F94B6F5@microsoft.com...
> > Chip,
> >
> > How would you set this to a range of cells in column A
> >
> >
> > "Chip Pearson" wrote:
> >
> >> You can use and event procedure to do this (see
> >> http://www.cpearson.com/excel/events.htm for more info about
> >> events). Right click the sheet tab and choose View Code. In
> >> the
> >> VBA code module that appears, paste the following code:
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >>
> >> If Target.Address = "$A$1" Then 'change cell as desired
> >> If Target.Value > 10 Then ' change criteria as desired
> >> Target.EntireRow.Hidden = True
> >> Else
> >> Target.EntireRow.Hidden = False
> >> End If
> >> End If
> >>
> >> End Sub
> >>
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "Davidjc52" <Davidjc52@discussions.microsoft.com> wrote in
> >> message
> >> news:F0E8927F-5563-4B80-AD01-04076625CCDB@microsoft.com...
> >> > We would like to be able to enter a value in a cell and if
> >> > it
> >> > meets our
> >> > predefined criteria the entire row would automatically hide.
> >> > Is
> >> > this possible
> >> > in excel?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>
>