| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hide sheets based on Cell value | Excel Discussion (Misc queries) | |||
| Is there a way to hide worksheets and/or rows/columns based on information enter into a particular cell of range of cells? | New Users to Excel | |||
| hide rows based on cell value | New Users to Excel | |||
| hide rows based on value in cell | Excel Discussion (Misc queries) | |||
| Is there a way to HIDE a row based on a value of a cell ? | Excel Discussion (Misc queries) | |||
Linear Mode
