how to hide a number of cells based on one cell?
 

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100 if A19
says "no"?

Thank you in advance,

TG

how to hide a number of cells based on one cell?
 

What you are asking for requires a macro. If you want to persue that then
right click the sheet tab, select view code and paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If LCase(Target.Value) = "no" Then
Rows("4:15").Hidden = True
Else
Rows("4:15").Hidden = False
End If
End If

If Target.Address = "$A$19" Then
If LCase(Target.Value) = "no" Then
Rows("20:100").Hidden = True
Else
Rows("20:100").Hidden = False
End If
End If

End Sub
--
HTH...

Jim Thomlinson


"TG" wrote:

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100 if A19
says "no"?

Thank you in advance,

TG

how to hide a number of cells based on one cell?
 

Let's assume that you are setting A1 thru typing (rather than a formula).
Enter the folowing event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A1")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
If a.Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200858


"TG" wrote:

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100 if A19
says "no"?

Thank you in advance,

TG

how to hide a number of cells based on one cell?
 

You can do that with the VBE. Right click on sheet tab, go to view code.
Paste this in, then close the editor:


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If

If Range("A19").Value = "no" Then
Range("A20:A100").EntireRow.Hidden = True
Else
Range("A20:A100").EntireRow.Hidden = False
End If

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"TG" wrote:

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100 if A19
says "no"?

Thank you in advance,

TG

how to hide a number of cells based on one cell?
 

Using a few less code lines...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
Application.EnableEvents = False
Range("A4:A15").EntireRow.Hidden = LCase(Target.Value) = "no"
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Let's assume that you are setting A1 thru typing (rather than a formula).
Enter the folowing event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A1")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
If a.Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200858


"TG" wrote:

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100 if
A19
says "no"?

Thank you in advance,

TG

how to hide a number of cells based on one cell?
 

If Range("A1").Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If

I thought you might be interested in knowing that the above 5 lines can be
replaced by this single line of code...

Range("A4:A15").EntireRow.Hidden = Range("A1").Value = "no"

--
Rick (MVP - Excel)


"Luke M" wrote in message
...
You can do that with the VBE. Right click on sheet tab, go to view code.
Paste this in, then close the editor:


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If

If Range("A19").Value = "no" Then
Range("A20:A100").EntireRow.Hidden = True
Else
Range("A20:A100").EntireRow.Hidden = False
End If

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"TG" wrote:

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100 if
A19
says "no"?

Thank you in advance,

TG

how to hide a number of cells based on one cell?
 

THanks all of you for your quick and helful inputs!

"Rick Rothstein" wrote:

If Range("A1").Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If

I thought you might be interested in knowing that the above 5 lines can be
replaced by this single line of code...

Range("A4:A15").EntireRow.Hidden = Range("A1").Value = "no"

--
Rick (MVP - Excel)


"Luke M" wrote in message
...
You can do that with the VBE. Right click on sheet tab, go to view code.
Paste this in, then close the editor:


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If

If Range("A19").Value = "no" Then
Range("A20:A100").EntireRow.Hidden = True
Else
Range("A20:A100").EntireRow.Hidden = False
End If

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"TG" wrote:

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100 if
A19
says "no"?

Thank you in advance,

TG

how to hide a number of cells based on one cell?
 

Very Clever!
--
Gary''s Student - gsnu200858


"Rick Rothstein" wrote:

If Range("A1").Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If

I thought you might be interested in knowing that the above 5 lines can be
replaced by this single line of code...

Range("A4:A15").EntireRow.Hidden = Range("A1").Value = "no"

--
Rick (MVP - Excel)


"Luke M" wrote in message
...
You can do that with the VBE. Right click on sheet tab, go to view code.
Paste this in, then close the editor:


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If

If Range("A19").Value = "no" Then
Range("A20:A100").EntireRow.Hidden = True
Else
Range("A20:A100").EntireRow.Hidden = False
End If

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"TG" wrote:

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100 if
A19
says "no"?

Thank you in advance,

TG

how to hide a number of cells based on one cell?
 

To make a pun... it seems quite logical to me.<g Since you are testing if
Range("A1").Value is True or False and then assigning whichever to the
Hidden property of the specified range, then we might as well make the
assignment directly and do away with the intermediate test.

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Very Clever!
--
Gary''s Student - gsnu200858


"Rick Rothstein" wrote:

If Range("A1").Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If

I thought you might be interested in knowing that the above 5 lines can
be
replaced by this single line of code...

Range("A4:A15").EntireRow.Hidden = Range("A1").Value = "no"

--
Rick (MVP - Excel)


"Luke M" wrote in message
...
You can do that with the VBE. Right click on sheet tab, go to view
code.
Paste this in, then close the editor:


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If

If Range("A19").Value = "no" Then
Range("A20:A100").EntireRow.Hidden = True
Else
Range("A20:A100").EntireRow.Hidden = False
End If

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"TG" wrote:

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100
if
A19
says "no"?

Thank you in advance,

TG

how to hide a number of cells based on one cell?
 

Gary,

How would I do this assuming that I am using a formula? for example I want
to use a check box and this check box gives me a true or false answer, I then
want to use that True or False answer but on another different spreadsheet in
the same way the "no" answer works. As of right now I cannot get the False or
True answer to work in my Micro, when I link my macro to lets say A1 (A1
being the cell the TRUE or FALSE from the check box is linked to) it does not
work, it only works if I type it in!

Got any suggestions??

Thanks Gary,

TG

"Gary''s Student" wrote:

Let's assume that you are setting A1 thru typing (rather than a formula).
Enter the folowing event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A1")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
If a.Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200858


"TG" wrote:

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100 if A19
says "no"?

Thank you in advance,

TG

how to hide a number of cells based on one cell?
 

Try the Private Sub Worksheet_Calculate() event instead.


Gord Dibben MS Excel MVP

On Thu, 25 Jun 2009 16:28:01 -0700, TG wrote:

Gary,

How would I do this assuming that I am using a formula? for example I want
to use a check box and this check box gives me a true or false answer, I then
want to use that True or False answer but on another different spreadsheet in
the same way the "no" answer works. As of right now I cannot get the False or
True answer to work in my Micro, when I link my macro to lets say A1 (A1
being the cell the TRUE or FALSE from the check box is linked to) it does not
work, it only works if I type it in!

Got any suggestions??

Thanks Gary,

TG

"Gary''s Student" wrote:

Let's assume that you are setting A1 thru typing (rather than a formula).
Enter the folowing event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A1")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
If a.Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200858


"TG" wrote:

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100 if A19
says "no"?

Thank you in advance,

TG

how to hide a number of cells based on one cell?
 

thanks,

ive tried this but it slows down excel significantly and sometimes it
freezes excel.

"Gord Dibben" wrote:

Try the Private Sub Worksheet_Calculate() event instead.


Gord Dibben MS Excel MVP

On Thu, 25 Jun 2009 16:28:01 -0700, TG wrote:

Gary,

How would I do this assuming that I am using a formula? for example I want
to use a check box and this check box gives me a true or false answer, I then
want to use that True or False answer but on another different spreadsheet in
the same way the "no" answer works. As of right now I cannot get the False or
True answer to work in my Micro, when I link my macro to lets say A1 (A1
being the cell the TRUE or FALSE from the check box is linked to) it does not
work, it only works if I type it in!

Got any suggestions??

Thanks Gary,

TG

"Gary''s Student" wrote:

Let's assume that you are setting A1 thru typing (rather than a formula).
Enter the folowing event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A1")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
If a.Value = "no" Then
Range("A4:A15").EntireRow.Hidden = True
Else
Range("A4:A15").EntireRow.Hidden = False
End If
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200858


"TG" wrote:

Hello,

How would I be able to hide lets say row 4-15 if A1 had the word no?
Is this even possible? Also, what if i also want to hide rows 20-100 if A19
says "no"?

Thank you in advance,

TG