Hiding/Exposing Rows based on a cell value

[Tami]

hi.
I need help writing a macro that will hide and unhide rows based on the
value in Cell A1. In the below example, If Cell A1=100, rows 3 and 4 would
be hidden and rows 1,2 and 5 would be unhidden. If cell A1=200 then only row
3 would be shown. Thanks in advance for your help, Tami

Column B
Row 1 100
Row 2 100
Row 3 200
Row 4 300
Row 5 100

 

[Don Guillett]

Tami,
You have had several answers to this basic question. Why are you posting
again.

 

[Tami]

Hi Don.
I had reviewed all my posts and I felt that this was a different scenario.
The past posts have taught me how to hide and unhide specific columns based
on a value in one cell...like unhide columns A:H or hide B:C so i feel
totally good about that code. But this latest reques is hiding rows based on
values in a column that runs down the left. I searched other posts to see if
there was a similar request but couldn't find a "match" but maybe i didn't
use good "search for" words.

 

[Don Guillett]

Assuming you want to do this yourself, you certainly should be able to
figure out how to substitute rows for columns and then test to see how it
works. That's how you learn by thinking and applying your thoughts. You eat
better when you learn to fish instead of just having fish thrown at you.

 

[Tami]

Don, maybe i'm missing something but i believe this to be a different
request. I've already capitalized on my learnings by re-writing macros
substituting rows for columns and they work. But there's no code within any
of my existing lessons that scan a column for a value then determine what to
hide. The examples that i've "learned to fish from" have dealt with IF a
cell value = x then hide specific rows and/or columns like HIDE A:H....under
this new scenario the rows would vary.
Let me do a new search to see if i can find an existing response that is
similar.

 

[Tami]

Don, it's not working but am i close?...
sincerely,
the fisherman

Sub HideRows()
Dim cell As Range
For Each cell In Range("b2:a100")
If cell.Value = "$a$1" Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next
End Sub

 

[Don Guillett]

I would probably do it something like this:

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Address <> Range("a1").Address Then Exit Sub
Rows.Hidden = False
Select Case Target
Case Is = 100: x = "3:5"
Case Is = 200: x = "3:6"
Case Else
End Select
Rows(x).Hidden = True
End Sub

 

[Tami]

Hello Don! Glad to hear from you! Please don't be mad but I fear that my
request was not clear:-|

first, the user could enter any number in cell a1....i don't know if its
100,200 or 999 so i don't think i can be so case-specific in the coding. I
fear that by putting an example i made it confusing because really the data
is ever changing.

Basically i need the macro to scan column B and if the cell = $a$1 then show
the row, if not hide it. does that help?

I'd also probably need to start at a specific row as well as i wouldn't want
some of my header rows to accidentally hide.
thanks again,
glad to have back on the "thread"!
tami

 

[Don Guillett]

You were given a loop to do that. It only needed to be changed fromIf you want to do these things without professional help you need to learn
to think of how to do it., test>test,
instead of asking others to do each thing for you. Learn to fish.

 

[Tami]

??? What loop was given to me? Get your facts straight.
I posted a macro.
You responded with another macro.
It didn't work. Your macro was too specific about select values.
I delicately tried to re-explain the request.

Please stop responding to my posts.
Your feedback is not contstructive and borderline inapprpriate given the
nature of this website.

 

[Don Guillett]

Last response. Go back in this very thread or down this very message to see
the loop that you were given. My last post only showed you what to change to
make it work. >> inapprpriate << And, you need to also learn to use your
spell checker. You must be very young.