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

 

[Richard Buttrey]

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
__________________________

 

[jgwphansen]

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.

 

[jgwphansen]

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

 

[Guest]

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

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

HTH,

Don