Macro that will unhide then hide rows
 

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.

Macro that will unhide then hide rows
 

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


--
HTH

Bob Phillips

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

"minka" wrote in message
...
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.

Macro that will unhide then hide rows
 

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.
--
Gary's Student


"Bob Phillips" wrote:

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


--
HTH

Bob Phillips

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

"minka" wrote in message
...
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.

Macro that will unhide then hide rows
 

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?

"Gary''s Student" wrote:

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.
--
Gary's Student


"Bob Phillips" wrote:

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


--
HTH

Bob Phillips

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

"minka" wrote in message
...
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.

Macro that will unhide then hide 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.
--
Gary's Student


"Plum" wrote:

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?

"Gary''s Student" wrote:

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.
--
Gary's Student


"Bob Phillips" wrote:

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


--
HTH

Bob Phillips

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

"minka" wrote in message
...
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.

Macro that will unhide then hide rows
 

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.

"Gary''s Student" wrote:

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.
--
Gary's Student


"Plum" wrote:

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?

"Gary''s Student" wrote:

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.
--
Gary's Student


"Bob Phillips" wrote:

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


--
HTH

Bob Phillips

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

"minka" wrote in message
...
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.

Macro that will unhide then hide rows
 

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.
--
Gary's Student


"Plum" wrote:

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.

"Gary''s Student" wrote:

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.
--
Gary's Student


"Plum" wrote:

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?

"Gary''s Student" wrote:

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.
--
Gary's Student


"Bob Phillips" wrote:

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


--
HTH

Bob Phillips

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

"minka" wrote in message
...
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.

Macro that will unhide then hide rows
 

a Selectcase macro or autofilter

--
Don Guillett
SalesAid Software

"Plum" wrote in message
...
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.

"Gary''s Student" wrote:

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.
--
Gary's Student


"Plum" wrote:

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?

"Gary''s Student" wrote:

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.
--
Gary's Student


"Bob Phillips" wrote:

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


--
HTH

Bob Phillips

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

"minka" wrote in message
...
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.

Macro that will unhide then hide rows
 

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)

"Plum" wrote in message
...
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.

"Gary''s Student" wrote:

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.
--
Gary's Student


"Plum" wrote:

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?

"Gary''s Student" wrote:

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.
--
Gary's Student


"Bob Phillips" wrote:

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


--
HTH

Bob Phillips

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

"minka" wrote in message
...
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.

Macro that will unhide then hide rows
 

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)

"Gary''s Student" wrote in message
...
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.
--
Gary's Student


"Bob Phillips" wrote:

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


--
HTH

Bob Phillips

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

"minka" wrote in message
...
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.

Macro that will unhide then hide rows
 

Not dynamic.

--
HTH

Bob Phillips

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

"Gary''s Student" wrote in message
...
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.
--
Gary's Student