Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can I automatically hide or filter a row?
I am using Excel 2000. I would like to create a worksheet that would
automatically hide certain rows depending on a value in a cell in a different row (or in the same row, if that would work better). I will be sharing the worksheet with others and I want to make it as user-friendly as possible. The worksheet would have, say, 3 groups of 26 rows. Some users may only use 6 or 7 rows in one group, and 10 rows in the second group, and perhaps no rows in the third group. Another user may use 10 rows in each of the three groups. Each user will have a different need. I would like to be able to enter a value into three cells and have the extra rows not appear in each group. I do not want the user to have to go to the toolbar to hide or filter out rows that are not used if that can be avoided. Is there any way to do this? Thanks. |
#2
|
|||
|
|||
Take a look at Auto Filter.
Click a cell in your database and go Data/Filter/Auto Filter Rgds, ScottO "RJQMAN" wrote in message ... | I am using Excel 2000. I would like to create a worksheet that would | automatically hide certain rows depending on a value in a cell in a different | row (or in the same row, if that would work better). I will be sharing | the worksheet with others and I want to make it as user-friendly as possible. | | The worksheet would have, say, 3 groups of 26 rows. Some users may only use | 6 or 7 rows in one group, and 10 rows in the second group, and perhaps no | rows in | the third group. Another user may use 10 rows in each of the three groups. | Each user will have a different need. I would like to be able to enter a | value into three cells and have the extra rows not appear in each group. I | do not want the user to have to go to the toolbar to hide or filter out rows | that are not used if that can be avoided. Is there any way to do this? | | Thanks. | |
#3
|
|||
|
|||
I read the information concerning auto-filter before I made my original post
(sorry I posted twice - I am a novice at these particular boards), but it still does not appear that I can have the worksheet 1) Do anything automatically with the auto-filter command, as I believe you have to go to the toolbar and manually request the filtering (I tested it with some changes to a reference cell, and it did not seem to pick up the changes automatically - I had to request it to filter again by going to the tool bar), and 2) If I understand it correctly, auto-filter enables me to shade a cell, but not hide a row. I am no expert for sure, and any and all help is appreciated. "ScottO" wrote: Take a look at Auto Filter. Click a cell in your database and go Data/Filter/Auto Filter Rgds, ScottO "RJQMAN" wrote in message ... | I am using Excel 2000. I would like to create a worksheet that would | automatically hide certain rows depending on a value in a cell in a different | row (or in the same row, if that would work better). I will be sharing | the worksheet with others and I want to make it as user-friendly as possible. | | The worksheet would have, say, 3 groups of 26 rows. Some users may only use | 6 or 7 rows in one group, and 10 rows in the second group, and perhaps no | rows in | the third group. Another user may use 10 rows in each of the three groups. | Each user will have a different need. I would like to be able to enter a | value into three cells and have the extra rows not appear in each group. I | do not want the user to have to go to the toolbar to hide or filter out rows | that are not used if that can be avoided. Is there any way to do this? | | Thanks. | |
#4
|
|||
|
|||
My apologies RJQ, I didn't read your post carefully enough.
You're right, the Auto Filter won't do what you want - it requires manual (or macro driven) intervention each time a cell is changed. Although not doing exactly what you want, as a work-around you could use Conditional Formatting to Black Out or otherwise indicate "Out of Bounds" cells. Good Luck, ScottO "RJQMAN" wrote in message ... | I read the information concerning auto-filter before I made my original post | (sorry I posted twice - I am a novice at these particular boards), but it | still does not appear that I can have the worksheet 1) Do anything | automatically with the auto-filter command, as I believe you have to go to | the toolbar and manually request the filtering (I tested it with some changes | to a reference cell, and it did not seem to pick up the changes automatically | - I had to request it to filter again by going to the tool bar), and 2) If I | understand it correctly, auto-filter enables me to shade a cell, but not hide | a row. | | I am no expert for sure, and any and all help is appreciated. | | "ScottO" wrote: | | Take a look at Auto Filter. | Click a cell in your database and go Data/Filter/Auto Filter | Rgds, | ScottO | | "RJQMAN" wrote in message | ... | | I am using Excel 2000. I would like to create a worksheet that would | | automatically hide certain rows depending on a value in a cell in a | different | | row (or in the same row, if that would work better). I will be sharing | | the worksheet with others and I want to make it as user-friendly as | possible. | | | | The worksheet would have, say, 3 groups of 26 rows. Some users may only | use | | 6 or 7 rows in one group, and 10 rows in the second group, and perhaps no | | rows in | | the third group. Another user may use 10 rows in each of the three | groups. | | Each user will have a different need. I would like to be able to enter a | | value into three cells and have the extra rows not appear in each group. | I | | do not want the user to have to go to the toolbar to hide or filter out | rows | | that are not used if that can be avoided. Is there any way to do this? | | | | Thanks. | | | | | |
#5
|
|||
|
|||
Is there a way to drive the filter command with a macro? How would I do this?
"ScottO" wrote: My apologies RJQ, I didn't read your post carefully enough. You're right, the Auto Filter won't do what you want - it requires manual (or macro driven) intervention each time a cell is changed. Although not doing exactly what you want, as a work-around you could use Conditional Formatting to Black Out or otherwise indicate "Out of Bounds" cells. Good Luck, ScottO "RJQMAN" wrote in message ... | I read the information concerning auto-filter before I made my original post | (sorry I posted twice - I am a novice at these particular boards), but it | still does not appear that I can have the worksheet 1) Do anything | automatically with the auto-filter command, as I believe you have to go to | the toolbar and manually request the filtering (I tested it with some changes | to a reference cell, and it did not seem to pick up the changes automatically | - I had to request it to filter again by going to the tool bar), and 2) If I | understand it correctly, auto-filter enables me to shade a cell, but not hide | a row. | | I am no expert for sure, and any and all help is appreciated. | | "ScottO" wrote: | | Take a look at Auto Filter. | Click a cell in your database and go Data/Filter/Auto Filter | Rgds, | ScottO | | "RJQMAN" wrote in message | ... | | I am using Excel 2000. I would like to create a worksheet that would | | automatically hide certain rows depending on a value in a cell in a | different | | row (or in the same row, if that would work better). I will be sharing | | the worksheet with others and I want to make it as user-friendly as | possible. | | | | The worksheet would have, say, 3 groups of 26 rows. Some users may only | use | | 6 or 7 rows in one group, and 10 rows in the second group, and perhaps no | | rows in | | the third group. Another user may use 10 rows in each of the three | groups. | | Each user will have a different need. I would like to be able to enter a | | value into three cells and have the extra rows not appear in each group. | I | | do not want the user to have to go to the toolbar to hide or filter out | rows | | that are not used if that can be avoided. Is there any way to do this? | | | | Thanks. | | | | | |
#6
|
|||
|
|||
Try this ...
Assuming that your workbook is called "Book2", and the relevant worksheet is called "Sheet2", and the range that you want filtered has been named "Table", and the 'Trigger Cell' has been named "Filtrum", and the column containing the filter criterion is the first column in the range named as "Table", then ... Open the Visual Basic Editor (Go Tools/Macro/Visual Basic Editor) If the Project Explorer pane isn't visible, go View/Project Explorer Double Click on VBA Project (Book2) Double Click on Sheet2 Paste this code into code area (I don't know the proper name) Private Sub Worksheet_Change(ByVal FilterNum As Range) Range("Table").AutoFilter Field:=1, Criteria1:=Range("FilterNum") End Sub Go back to your worksheet and change the value in FilterNum. Hopefully you'll see the Auto Filter do it's thing. Good Luck. Rgds, ScottO "RJQMAN" wrote in message ... | Is there a way to drive the filter command with a macro? How would I do this? | | "ScottO" wrote: | | My apologies RJQ, I didn't read your post carefully enough. | | You're right, the Auto Filter won't do what you want - it requires manual | (or macro driven) intervention each time a cell is changed. | | Although not doing exactly what you want, as a work-around you could use | Conditional Formatting to Black Out or otherwise indicate "Out of Bounds" | cells. | | Good Luck, | ScottO | | "RJQMAN" wrote in message | ... | | I read the information concerning auto-filter before I made my original | post | | (sorry I posted twice - I am a novice at these particular boards), but it | | still does not appear that I can have the worksheet 1) Do anything | | automatically with the auto-filter command, as I believe you have to go to | | the toolbar and manually request the filtering (I tested it with some | changes | | to a reference cell, and it did not seem to pick up the changes | automatically | | - I had to request it to filter again by going to the tool bar), and 2) If | I | | understand it correctly, auto-filter enables me to shade a cell, but not | hide | | a row. | | | | I am no expert for sure, and any and all help is appreciated. | | | | "ScottO" wrote: | | | | Take a look at Auto Filter. | | Click a cell in your database and go Data/Filter/Auto Filter | | Rgds, | | ScottO | | | | "RJQMAN" wrote in message | | ... | | | I am using Excel 2000. I would like to create a worksheet that would | | | automatically hide certain rows depending on a value in a cell in a | | different | | | row (or in the same row, if that would work better). I will be | sharing | | | the worksheet with others and I want to make it as user-friendly as | | possible. | | | | | | The worksheet would have, say, 3 groups of 26 rows. Some users may | only | | use | | | 6 or 7 rows in one group, and 10 rows in the second group, and perhaps | no | | | rows in | | | the third group. Another user may use 10 rows in each of the three | | groups. | | | Each user will have a different need. I would like to be able to | enter a | | | value into three cells and have the extra rows not appear in each | group. | | I | | | do not want the user to have to go to the toolbar to hide or filter | out | | rows | | | that are not used if that can be avoided. Is there any way to do | this? | | | | | | Thanks. | | | | | | | | | | | | |
#7
|
|||
|
|||
Thank you. I will run some tests and see how it goes. I appreciate your
help. I will post here when I get the total problem solved. Other advice is appreciated as well. "ScottO" wrote: Try this ... Assuming that your workbook is called "Book2", and the relevant worksheet is called "Sheet2", and the range that you want filtered has been named "Table", and the 'Trigger Cell' has been named "Filtrum", and the column containing the filter criterion is the first column in the range named as "Table", then ... Open the Visual Basic Editor (Go Tools/Macro/Visual Basic Editor) If the Project Explorer pane isn't visible, go View/Project Explorer Double Click on VBA Project (Book2) Double Click on Sheet2 Paste this code into code area (I don't know the proper name) Private Sub Worksheet_Change(ByVal FilterNum As Range) Range("Table").AutoFilter Field:=1, Criteria1:=Range("FilterNum") End Sub Go back to your worksheet and change the value in FilterNum. Hopefully you'll see the Auto Filter do it's thing. Good Luck. Rgds, ScottO "RJQMAN" wrote in message ... | Is there a way to drive the filter command with a macro? How would I do this? | | "ScottO" wrote: | | My apologies RJQ, I didn't read your post carefully enough. | | You're right, the Auto Filter won't do what you want - it requires manual | (or macro driven) intervention each time a cell is changed. | | Although not doing exactly what you want, as a work-around you could use | Conditional Formatting to Black Out or otherwise indicate "Out of Bounds" | cells. | | Good Luck, | ScottO | | "RJQMAN" wrote in message | ... | | I read the information concerning auto-filter before I made my original | post | | (sorry I posted twice - I am a novice at these particular boards), but it | | still does not appear that I can have the worksheet 1) Do anything | | automatically with the auto-filter command, as I believe you have to go to | | the toolbar and manually request the filtering (I tested it with some | changes | | to a reference cell, and it did not seem to pick up the changes | automatically | | - I had to request it to filter again by going to the tool bar), and 2) If | I | | understand it correctly, auto-filter enables me to shade a cell, but not | hide | | a row. | | | | I am no expert for sure, and any and all help is appreciated. | | | | "ScottO" wrote: | | | | Take a look at Auto Filter. | | Click a cell in your database and go Data/Filter/Auto Filter | | Rgds, | | ScottO | | | | "RJQMAN" wrote in message | | ... | | | I am using Excel 2000. I would like to create a worksheet that would | | | automatically hide certain rows depending on a value in a cell in a | | different | | | row (or in the same row, if that would work better). I will be | sharing | | | the worksheet with others and I want to make it as user-friendly as | | possible. | | | | | | The worksheet would have, say, 3 groups of 26 rows. Some users may | only | | use | | | 6 or 7 rows in one group, and 10 rows in the second group, and perhaps | no | | | rows in | | | the third group. Another user may use 10 rows in each of the three | | groups. | | | Each user will have a different need. I would like to be able to | enter a | | | value into three cells and have the extra rows not appear in each | group. | | I | | | do not want the user to have to go to the toolbar to hide or filter | out | | rows | | | that are not used if that can be avoided. Is there any way to do | this? | | | | | | Thanks. | | | | | | | | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically filter out blanks in multiple columns | Excel Worksheet Functions | |||
Help with a hide row macro (not using filter) | Excel Worksheet Functions | |||
How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions | |||
Filter Switches vs Macro? | Excel Discussion (Misc queries) | |||
How do I automatically hide rows | Excel Discussion (Misc queries) |