Hiding rows based on a column value

[Mark Hansen]

I am new to this group and VBA. I didn't see a way to search past posts
for this question so if there is a way I missed, please let me know.

I am trying to create a loop that works through the values of a single
column range and then hides rows based on the value of a string in each
cell. The range name is "Vendor" and I want to be able to only view one
vendor at a time. Maybe approaching it from a filtering position rather
than hiding the row might be easier.

Any advice?

[Don Guillett]

Use data>filter>autofilter

--
Don Guillett
SalesAid Software
[email protected]
"Mark Hansen" <[email protected]> wrote in message
news:[email protected]...
>I am new to this group and VBA. I didn't see a way to search past posts
>for this question so if there is a way I missed, please let me know.
>
> I am trying to create a loop that works through the values of a single
> column range and then hides rows based on the value of a string in each
> cell. The range name is "Vendor" and I want to be able to only view one
> vendor at a time. Maybe approaching it from a filtering position rather
> than hiding the row might be easier.
>
> Any advice?
>

[Richard Buttrey]

On Sat, 08 Apr 2006 14:41:36 -0700, [email protected] (Mark
Hansen) wrote:

>I am new to this group and VBA. I didn't see a way to search past posts
>for this question so if there is a way I missed, please let me know.
>
>I am trying to create a loop that works through the values of a single
>column range and then hides rows based on the value of a string in each
>cell. The range name is "Vendor" and I want to be able to only view one
>vendor at a time. Maybe approaching it from a filtering position rather
>than hiding the row might be easier.
>
>Any advice?


I have a similar requirement in one of my applications. I have a range
of cells in a single column each of which evaluate as either "hide" or
"show". i.e they test for the condition that all the cells in a row
are zero with IF(SUM(C10:EK10)<>0,"show","hide")

I have a general purpose macro - see below, which is passed a range of
cells to evaluate, and a Boolean True/False which will either hide or
show the range in question


Public Sub Hide_Rows(Myrows As Range, YN As Boolean)
Dim rowcount As Integer
Dim n As Integer

rowcount = Myrows.Count
If YN = False Then
Myrows.Rows.Hidden = YN
Else
For n = 1 To rowcount
If Myrows.Cells(n, 1) = "hide" Then
Myrows.Cells(n, 1).EntireRow.Hidden = True
End If
Next
End If
End Sub


You could extend this and also pass your vendor code as a third
argument. Then use this third argument as a test, and instead of

If Myrows.Cells(n, 1) = "hide" Then...

use

If Myrows.Cells(n, 1) = vendorcode Then...


HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[[email protected]]

Thanks, this worked great.

and now that I have access through google I can search before I ask
next time. Glad to find such a resource.
Richard Buttrey wrote:
> On Sat, 08 Apr 2006 14:41:36 -0700, [email protected] (Mark
> Hansen) wrote:
>
> >I am new to this group and VBA. I didn't see a way to search past posts
> >for this question so if there is a way I missed, please let me know.
> >
> >I am trying to create a loop that works through the values of a single
> >column range and then hides rows based on the value of a string in each
> >cell. The range name is "Vendor" and I want to be able to only view one
> >vendor at a time. Maybe approaching it from a filtering position rather
> >than hiding the row might be easier.
> >
> >Any advice?
>
>
> I have a similar requirement in one of my applications. I have a range
> of cells in a single column each of which evaluate as either "hide" or
> "show". i.e they test for the condition that all the cells in a row
> are zero with IF(SUM(C10:EK10)<>0,"show","hide")
>
> I have a general purpose macro - see below, which is passed a range of
> cells to evaluate, and a Boolean True/False which will either hide or
> show the range in question
>
>
> Public Sub Hide_Rows(Myrows As Range, YN As Boolean)
> Dim rowcount As Integer
> Dim n As Integer
>
> rowcount = Myrows.Count
> If YN = False Then
> Myrows.Rows.Hidden = YN
> Else
> For n = 1 To rowcount
> If Myrows.Cells(n, 1) = "hide" Then
> Myrows.Cells(n, 1).EntireRow.Hidden = True
> End If
> Next
> End If
> End Sub
>
>
> You could extend this and also pass your vendor code as a third
> argument. Then use this third argument as a test, and instead of
>
> If Myrows.Cells(n, 1) = "hide" Then...
>
> use
>
> If Myrows.Cells(n, 1) = vendorcode Then...
>
>
> HTH
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

[[email protected]]

I like the results of this approach but don't like the look of the drop
down options for every heading. Is there a way to achieve the same
results but not have the drop down options available for the user to
adjust? I want to have a little more control over the interface.

[Don Guillett]

try this which sorts and then hides all but what is in b1. assign to buttons
or assign the 1st on to a worksheet_change event. You could have mv be the
result of an inputbox.

Sub sortandhide()
Cells.EntireRow.Hidden = False
Range("a2:a" & Cells(Rows.Count, "a").End(xlUp).Row).Sort key1:=Range("a2")
mv = Range("b1") '"yourvalue"
fr = Columns(1).Find(mv).Row
'MsgBox fr
If fr > 2 Then Rows("2:" & fr - 1).EntireRow.Hidden = True
lr = Application.Match(mv, Columns(1))
'MsgBox lr
Rows(lr + 1 & ":" & Cells(Rows.Count, "a").End(xlUp).Row +
1).EntireRow.Hidden = True
End Sub

Sub unhiderows()
Cells.EntireRow.Hidden = False
End Sub

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
>I like the results of this approach but don't like the look of the drop
> down options for every heading. Is there a way to achieve the same
> results but not have the drop down options available for the user to
> adjust? I want to have a little more control over the interface.
>

[Don G]

You might want to look at this Add-In by Ron DeBruin....

http://www.rondebruin.nl/easyfilter.htm

HTH,

Don

"[email protected]" wrote:

> Thanks, this worked great.
>
> and now that I have access through google I can search before I ask
> next time. Glad to find such a resource.
> Richard Buttrey wrote:
> > On Sat, 08 Apr 2006 14:41:36 -0700, [email protected] (Mark
> > Hansen) wrote:
> >
> > >I am new to this group and VBA. I didn't see a way to search past posts
> > >for this question so if there is a way I missed, please let me know.
> > >
> > >I am trying to create a loop that works through the values of a single
> > >column range and then hides rows based on the value of a string in each
> > >cell. The range name is "Vendor" and I want to be able to only view one
> > >vendor at a time. Maybe approaching it from a filtering position rather
> > >than hiding the row might be easier.
> > >
> > >Any advice?
> >
> >
> > I have a similar requirement in one of my applications. I have a range
> > of cells in a single column each of which evaluate as either "hide" or
> > "show". i.e they test for the condition that all the cells in a row
> > are zero with IF(SUM(C10:EK10)<>0,"show","hide")
> >
> > I have a general purpose macro - see below, which is passed a range of
> > cells to evaluate, and a Boolean True/False which will either hide or
> > show the range in question
> >
> >
> > Public Sub Hide_Rows(Myrows As Range, YN As Boolean)
> > Dim rowcount As Integer
> > Dim n As Integer
> >
> > rowcount = Myrows.Count
> > If YN = False Then
> > Myrows.Rows.Hidden = YN
> > Else
> > For n = 1 To rowcount
> > If Myrows.Cells(n, 1) = "hide" Then
> > Myrows.Cells(n, 1).EntireRow.Hidden = True
> > End If
> > Next
> > End If
> > End Sub
> >
> >
> > You could extend this and also pass your vendor code as a third
> > argument. Then use this third argument as a test, and instead of
> >
> > If Myrows.Cells(n, 1) = "hide" Then...
> >
> > use
> >
> > If Myrows.Cells(n, 1) = vendorcode Then...
> >
> >
> > HTH
> > __
> > Richard Buttrey
> > Grappenhall, Cheshire, UK
> > __________________________
>
>