Hide Rows From Dropdown
May 22, 2009
i have a sheet with three dashboards built in and want to dispay on certain data depending on what is selected in a drop down menu (E8)
All - All rows are unhidden
UKIRSA - Rows 133 to 192
Glasgow - Rows 72 to 92
Sykes - Rows 12 to 70
Header - Rows 1 to 11
Footer - Rows 193 onwards
I want the header and footer to remain the same but the middle body of the dashboard to change depending on the selected option ie only the rows for the option are displayed, eg if Sykes selected then Glasgow and UKIRSA ranges are hidden
This is the code i am trying but it is not working:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "E8" Then
Select Case UCase(Target.Value)
Case "Sykes": Rows("12:70").Hidden = False
Case "Sykes": Rows("72:192").Hidden = True
Case "Glasgow": Rows("12:70").Hidden = True
Case "Glasgow": Rows("72:132").Hidden = False
Case "UKIRSA": Rows("134:192").Hidden = False
Case "UKIRSA": Rows("12:132").Hidden = True
Case "All": Rows("12:192").Hidden = False
End Select
End If
End Sub
View 9 Replies
ADVERTISEMENT
Aug 21, 2014
Goal: Hide rows based on value in Drop Down Box
Problem: The code works fine if i manually change the $D$10. If I make changes to the Drop Down Box the cell link updates, but the rows are not hidden.
[Code] .....
View 6 Replies
View Related
Jan 8, 2005
I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.
View 9 Replies
View Related
Feb 1, 2013
I have a worksheet that allows you to choose 1-20... depending on the selection it should unhide specific rows but I keep getting errors...
Code that will allow you to unhide other worksheets in the same book????
View 1 Replies
View Related
Jun 11, 2014
I have a spreadsheet which contains our fleet information covering many vehicles in one list. The teams for these vehicles are listed in Column A. What I would like to have is:
A dropdown box to pick from All, or the various teams in A1To hide every row that doesn't match the selection of A1To reveal every row that contains the selection for A1 at any point in column A. e.g: "*Dog Team*"
This is because something might be listed as "Moved from Dog Team" or "Dog Team, temporarily on loan", or "Dog Team V66.m"
I have taken a small sample of the data I will be working from and made a very simple drop down box which displays the pages of information for the vehicles by hiding/unhiding blocks of columns:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Range("A2") = "MOT/Service" Then
ActiveSheet.Columns("A:P").EntireColumn.Hidden = False
ActiveSheet.Columns("Q:CE").EntireColumn.Hidden = True
ElseIf Range("A2") = "Contact Details" Then
[Code]....
But because there is also filters on the sheet, meaning the positions of the entries based on rows can change, I need it to search for the "*xyzzy*" method, but am unsure how to do this! While just using the Filters will work, due to some human limitations I have been asked to have a specific drop down box in a very specific location with instructions next to it.
View 1 Replies
View Related
May 26, 2014
I have a macro in which i can enter the rows i want to hide.
If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].
Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.
View 14 Replies
View Related
Jun 9, 2013
Sub Button294_Click()
If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub
View 4 Replies
View Related
Nov 14, 2008
I trying to do a sheet with one combo box (dropdown list) where I want the user to be able to choose "add rows" to make more boxes appear. My idea to solve this was to put all the boxes in the sheet, and then create a macro that either hides or shows the rows with the extra boxes. Now I have a problem that hiding the rows just does that, and only that. The rows disappear, but the combo boxes stay visible (but ends up on top of eachother).
View 2 Replies
View Related
Jan 31, 2012
I have seen in a number of spreadsheets that you can create a drop down list using data validation that hides certain columns depending on what you select. this leads me on to my question...
I have created a Gantt chart for an entire year and what to put a drop down box in C1 that contains four selections (Q1, Q2, Q3, Q4) to represent the four quarters of the year. I want to work it so that when the user selects Q1 it hides the columns where the other Quarters of the year are kept so you can only see that quarter.
If that is possible I would then like a second drop down box that allows the user to select a specific week.
Q1 is columns AW:DI.
I'm using excel 2010.
View 1 Replies
View Related
Oct 5, 2009
Is there a way to have a column unhide one choosing a certain value from a dropdown list (validation)? I want the column to be hidden throughout the worksheet until a specific value is chosen in the list. Once it's chosen then I want it to unhide that column.
View 4 Replies
View Related
Feb 5, 2013
I am looking to allow a user to enter a unique value when a drop down value is captured from a list i.e 'Other' (Possibly a pop up box), then I require this value to be hidden and calculated along with others to show the average (The average calculation obviously wouldn't be in the same cell).
View 8 Replies
View Related
Mar 4, 2008
I've done some looking around in the object browser and in the forum, but i wasn't able to find the answer to this question:
I've got a combobox (dropdown list) in a userform which i use to run macros. soft of them are fairly long (2-3seconds), and during that time the dropdown list remains visible.... is there a way to force hide it?
View 6 Replies
View Related
Aug 5, 2014
I want an option, if click on drop down button on parent cell than few a cells will come out in which I have mentioned details of the parent cell.
By pressing the same button again those cells will be hidden or covered under parent cell.
View 14 Replies
View Related
Sep 26, 2008
I am a newbie to Excel. I really appreciate if someone could help me here and this is very urgent as I have a project going on.
I have a cell say B3, which is a dropdown list I created using Data Validation. It only lists down Yes or No.
What I need is when you click on Yes in B3, i need columns C to I to unhide. If answer is No, column C to I should be hidden. By default, the columns will be hidden.
Can you please help? I tried many times but not successful. Appreciate if someone will be able to walk me through details. If you want to see the sample spreadsheet, I can sent it to you by email. I tried Data> Outline group but they are not happy with this.
View 9 Replies
View Related
Feb 26, 2014
I am hoping to create a drop down list of months in one sheet, and when I select a certain month, columns in about 10 other worksheets in the same workbook will either hide or unhide columns...
The spreadsheet is laid out with columns (C-N) for each month in the year, for actuals, then columns for budget and budget variance (O-P), then YTD Actual, YTD Budget and YTD Variance. When I select September, for example, I want October-December to hide, and leave Jan-Sep unhidden, while keeping the budget, YTD and variance columns.
Is there a VBA code that can achieve this?
View 14 Replies
View Related
Nov 3, 2006
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
View 2 Replies
View Related
Jan 30, 2014
I have a workbook with 7 different Sheets to pull data from. On Sheet 3 named "PMP Mileage Linear". I have 4 drop down boxes. In one drop down labeled "Drop Down 4" there are 21 different choices to choose from. Based on the choice I would like to hide or unhide Rows. Example: if a user chooses "3yr/36,000 Miles". I would like to hide rows 16:51. If a user chooses "3yr/60,000 Miles". I would like to hide rows 29:51 (hence un-hiding 16:28). If a user chooses "5yr/100,000 Miles" I would like only rows 41:51 hidden and so on until a user hits the max of 125,000 Miles (5,6 or 7Yr) whereas no rows are hidden. I would have attached the spreadsheet but I don't have permission from the people who own the data.
View 7 Replies
View Related
Nov 28, 2011
I created a Validation list from 2 to 20. I want to have it (the list) add a row every time you select the proper data.
Example: If I want to add 2 row I would select the 2 in the drop down menu. If I need 5 rows I need to select 5 from the drop down menu.
Is this a VBA code? Or can I use a formula?
View 5 Replies
View Related
Jul 4, 2013
I have been asked to edit a worksheet for a client, they want me to create a dependent drop list using existing data from three columns.
I have created a dynamic named range and used and index to create the drop down list, which seems to work correctly. I run into a problem with I try to add the list to more than one column, as it obviously pulls in values from the Helper sheet I have used.
When the user tries to select values in row one after making a selection in row two, the values available for selection are incorrect.
Is there any way to have a three column dependent drop list that can be used in more than one row?
I have uploaded the file here: ownCloud
The information on the tab named 'Cabling Ducts' is used in an index on the 'Helper' sheet to remove duplicate values. The drop down list columns appear in 'CS 1' under the headings 'Cable Type', 'Size (mm)' and 'Cores'. Helper sheet is self explanatory.
View 1 Replies
View Related
Jul 17, 2014
I have a worksheet that has a quantity column "A" and if there is no value in it I would like to hide all rows without values so only rows with quantities remain. Based on the code I have supplied below you will notice I have ranges of cells that I want to work with. The code I have works well with the exception it takes a long time maybe 30-45 seconds to perform the operation. I was wondering if there is a faster way to perform the function. I have saved it as a macro which I tied to a button.
[Code] .....
View 9 Replies
View Related
Oct 6, 2008
I have sheet 1 and 2 in a work book. Sheet 1 is a list of data consisting of names and address, and quantity, type, price, etc of items shipped to them. Sheet 2 takes the data from sheet one and computes certain formulas. I have allotted up to 100 rows of data to automatically compute, however I rarely need that much. Basically it goes like this. When I open the spreadsheet sheet 1 is not actually blank, instead in cell A1 I have the phrase "Customer Name", in cell B1 I have the phrase "Account Number" and so one. I do this so my coworkers know which info must go in which cells. As long as the info is put in the correct spot than cell A1 in sheet 2 with do X and B1 will do Y and so on. so if I put in 10 customers with accompanying data then the first 10 rows of sheet 2 will compute formulas and the other 90 rows will simple state "No Data" in the cells. What I would like to do is create a macro or assign a command button to HIDE all the rows that say "No Data" in column A. So if I have 30 customers entered then when I go to Sheet 2 all I have to do is hit a button and rows 31-100 become HIDDEN, not deleted.
View 3 Replies
View Related
Feb 22, 2013
I preferrably want to hide all rows from row 58 and down, but then unhide some rows. The rows that I want to unhide is from row 1000. How many rows that I want to unhide, depends on the content of that list. If the list is empty, then I want to unhide rows 1000-1003.
What I have written (but doesn't work), is:
Code:
Range("B58:AG1048576").Select
Selection.EntireRow.Hidden = True
If Range("C1002") = Empty Then
Erstatningsgrunnlag = Range("B1000:AG1003")
Else: Erstatningsgrunnlag = Range("C1001:AG") & Range("C1001").End(xlDown).Row + 1
End If
Erstatningsgrunnlag.EntireRow.Hidden = False
View 3 Replies
View Related
Oct 29, 2013
Rows 9-79 - if value in column D is zero, then hide row.
How do I do this?
Also - can this be triggered by just selecting the worksheet, or will I have to use a button?
View 4 Replies
View Related
Aug 21, 2007
Is there such a way/function that i could simple hide/exclude any row with ZERO value in either a pivot table or in a regalur table of data? As of now, i have to manually find the row with zero value and hide them individually.
View 9 Replies
View Related
Jan 8, 2008
I have a worksheet "ULIP21.xls", where in cell C10, the value can be either "Yes" or "No". If the value is "No", I want the rows 31 to rows 82 hidden in the sheet "INPUT" of the worksheet.
View 9 Replies
View Related
Feb 13, 2009
I want to make a macro that will recognize a value in a cell and then hide
rows that I don't need.
For example: cell is Y2
If I put into the cell the value of "abs" i whant to hide rows from below that do not contains(the rows are Y5 :Y25) "abs"
View 9 Replies
View Related
Jun 17, 2009
i've run a quick search on the forum and have picked up a few ideas but wonder if someone can help write some code?
I need rows 10:14 to hide in sheet2 if cell b13 in sheet 1 is blank?
View 9 Replies
View Related
Sep 13, 2009
I'm creating a roster which effectively needs 5 discrete pieces of information per cell - one main piece and 4 notes.
The creator of the current worksheet used comments for this function. They can't be printed, searched etc, you can't work with them and because the info can't be added with data validation it's inconsistent. What a nightmare.
I've rejected the 3d option across sheets because of the complexity and limitations of 3d calculations.
My solution is to have the main info in row 1 and the other 4 items in rows 2-5 and then repeat downwards so there are 5 rows per day.
My question is this - is there an easy way for users to hide and unhide the 4 extra rows? I need to improve readability but the extra info needs to be quickly and easily accessed by some users. Everyone else just needs to see what they are doing each day. This roster is a year long downwards so manual hiding and unhiding rows isn't practical.
e.g.:
1camerasounddirectetc>>>2hide details monwho is actually workingmarytomdick3who should have beenmungomidge4why the changesicknessholiday5type of coverovertimefreelance6details of changedouble time invoice no 10247hide details tuewho is actually workingharrymungodick8who should have beenmarymidge9why the changeswapped shiftsholiday10type of coverstafffreelance11details of change40080 invoice no 102412etc13 /
and when hidden, this:
1camerasounddirectetc>>>2show detailsmonwho is actually workingmarytomdick3show detailstuewho is actually workingharrymungodicketc /
View 9 Replies
View Related
Jan 21, 2010
I run a report everyday that I have to sort many times and remove unwanted rows before its down to the data that I need. What I want to try and accomplish is to say that IF column B or C contains WORD1 or WORD2 to hide that ROW that its in. Also if Column D is greater than 400 to hide that row. Better yet delete it! Below would be an example of raw data, then below it what I would only want to show:
Advisory
NIGO
IGO
127.6712/07/2009 10:1612/07/2009 12:23Retail
IGO
IGO
117.412/07/2009 10:2712/07/2009 12:24Advisory
IGO
PENDING
125.0712/07/2009 10:2312/07/2009 12:28Retail
IGO
IGO
422.6512/07/2009 10:3112/07/2009 12:34Advisory
IGO
IGO
82.5712/07/2009 11:2512/07/2009 12:47
Worksheet would just show:
Retail
IGO
IGO
117.412/07/2009 10:2712/07/2009 12:24Advisory
IGO
IGO
82.5712/07/2009 11:2512/07/2009 12:47
View 9 Replies
View Related
Jan 29, 2010
I have a workbook with a number of worksheets that come in twice a week. On each sheet the layout is identical as shown below with the data for each group of cities starting at row 4 for 4 rows and then continuing for a few hundred rows without a break. The city in A4 and every 4th row is merged with the 3 rows below
Unfortunately the cities do tend to change so a recorded macro would not be reliable.
A............................. B
Birmingham.....line title
merged.......... line title
merged.......... line title
merged.......... line title
Glasgow........ line title
merged................ line title
merged................ line title
merged............... line title
What I would like is to list the cities that I am interested in in the code and for it then to hide all of the other blocks of 4 rows for cities not listed.
So using the above example if I Birmingham was not listed in the code then its 4 lines would be hidden and only Glasgow would be visiable
View 9 Replies
View Related
