Macro that will unhide then hide rows

M

minka

Hi,

Would someone be so kind as to assist..please

I am trying to create a macro that will unhide and hid rows dependant on the
content of column A.
 
B

Bob Phillips

Rows("5:6").Hidden = Range("A1").Value = "value"


--
HTH

Bob Phillips

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

Gary''s Student

This goes in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 99 Then
Rows(10).EntireRow.Hidden = True
Else
Rows(10).EntireRow.Hidden = False
End If
End Sub


If cell A5 becomes 99 then row 10 is hidden, otherwise it is un-hidden.
 
P

Plum

Yes, this is good, but if you have 50 rows, you need to repeat the process 50
times! There must be a more efficient way of doing this with a large number
of rows?
 
G

Gary''s Student

Hi Plum:

We can always hide a bunch of rows in one swell foop:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 99 Then
Rows("10:60").EntireRow.Hidden = True
Else
Rows("10:60").EntireRow.Hidden = False
End If
End Sub

So fifty rows can be hidden with no more code than one row.
 
P

Plum

Yes, but what if you need the hiding of each row to be dependent on the cell
in column A? For example; A1 = 99 and therefore should be hidden, but A2 =
80, and should be shown, etc. etc... You would have to repeat the lines of
code 50 times in that case? (I know I'm going off on a tangent a bit but
it's something I"ve been trying to work out for ages!)
Regards.
 
G

Gary''s Student

If we want the hidden/unhidden state of each row in a range to depend on the
value in column A, then consider using AutoFIlter.
 
B

Bob Phillips

Then you would do on the condition in A, wiothout the repeats you dread

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A50"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.entireow.Hidden = .Value = 99
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



--
HTH

Bob Phillips

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

Bob Phillips

Using my style of code

Private Sub Worksheet_Change(ByVal Target As Range)
Rows(10).EntireRow.Hidden = Range("A1").Value = 99
End Sub

but you should also be using Target somewhere as you are monitoring the
change event.

--
HTH

Bob Phillips

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

Bob Phillips

Not dynamic.

--
HTH

Bob Phillips

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