Macro that will unhide then hide rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Rows("5:6").Hidden = Range("A1").Value = "value"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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.
 
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?
 
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.
 
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.
 
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.
 
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)
 
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)
 
Not dynamic.

--
HTH

Bob Phillips

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