I am trying to work out a macro to hide and unhide rows in worksheet 6 based on a cell input (cell I6) in worksheet 1 based on the following:
>> If I6 in Worksheet 1 = 1 or 5 or 6 then Hide Rows 19 to 24 in Worksheet 6
>> If I6 in Worksheet 1 = 2 then Hide Rows 21 to 24 in Worksheet 6
>> If I6 in Worksheet 1 = 3 or 4 or 7 then Hide Rows 17 to 20 in Worksheet 6
>> If I6 in Worksheet 1 = 8 or 9 then Hide Rows 17 to 20 and Rows 23 to 24 in Worksheet 6
I am trying to find code that will allow me to hide a set number of rows based on the value of a specific cell which I need to work for two worksheets in the same workbook. Is that even possible?
For example: when i enter 5 into cell D1, I need five rows to be visible on both sheets.
I have a problem with hiding and unhiding columns. Is there a way wherein I can automatically hide the row(s) if the cell = "N/A" and will unhide if it's not equal to "NA". see the attachment Im using excel 2003 btw. You can see in Sheet2 to that it contains the table for the student grades. "N/A" means that they are not enrolled on that subject. While in sheet1, What I want to happen is that you will select a student name and below, it will automatically display the grades of the student on their enrolled subjects only. So, those fields with "N/A" will hide and when I update the value of N/A it will unhide.
For data validation lists B27 to B30, if any of them is equal to "EBS via ULL" then unhide rows 31 to 42. If any of the data validation list not equal to "EBS via ULL" then unhide row 44 and hide rows 31 to 42. If "EBS via ULL" and any other product in the lists is selected, unhide rows 31 to 44. If validation lists B27 to B30 are all equal to "Select Product..", then hide rows 31 to 44. I would like to use the worksheets_change event.
I am trying to Hide and Unhide several rows depending on if several checkboxes are marked or not.
[Code] .....
This is the code I am using, which is effective in hiding and unhiding the rows. However, the issue I am having is: I want Row 20 to stay visible if either one of the checkboxes is marked. This string of code runs the "hide" portion if either box is unchecked.
I would need two macros for one of my projects. I did search the forum, but I couldn't find anything what would suit my needs.
The first macro should hide those rows which would contain 0 (zero) in a specific column (in my workbook it's E). The secon macro should unhide the hidden rows.
coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.
As a simplified example:
Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!
So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.
I was thinking of using something like this:
Rows("20:30,40:50").EntireRow.Hidden = True If Target.Address="'Worksheet1'!A1" Then If Target.Value = "Set 1" Then Rows("40:50").EntireRow.Hidden = False Else Rows("20:30").EntireRow.Hidden = False End If End If
I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.
I have groups of data in A5:AW200 with various blank rows in between, and would like to be able to automatically hide and unhide (toggle) the blank rows, but only for those rows where cells in column A are blank.
I'd also like to hide those same groups of blank rows on Sheets 2-5, which are laid out exactly the same as Sheet 1, but since they contain cell references to and are mirrors of Sheet 1, certain rows on those sheets may appear blank, but in fact are not. (I should probably have separate buttons on each sheet, but not sure). I know Sheet 1 can be used to test for conditions (blanks rows) and translate the results to Sheets 2-5, but I'm not sure how to do it.
I'm trying to hide all columns which have the word "hide" in row 6. I have done a similar thing whereby I hide all rows which have the word "hide" in column 3 using the following
Sub HURows() BeginRow = 9 EndRow = 40 ChkCol = 3 For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "hide" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt End Sub
Alas changing the number and every Col for Row and vice versa doesn't work!! Really I only need to search colums G to U inclusive. The code must also unhide colums if the values in the cells of row 6 change to anything other than "hide".
My Workbook has 72 worksheets split into two. The first 36 include all the data, but the second 36 have one element removed.
I am very grateful to RoyUK and others who have steered me towards some code that allows me to hide and unhide the first 36 sheets exactly as I want, but when I add the second 36 the code comes up with an error saying the Procedure is too large.
I did want to split the code into two parts activated by two validated drop down menus, but this did not work either.
If I have two drop down menus at D8 and G8 is it possible to have two separate codes as follows
Is it possible to set up a hyperlink for a cell, when clicking on it, hides/unhides certain rows below that cell. For example, having a full employee roster, each name having 15 rows underneath. By making the Name cell hyperlink, and by clicking on it, I would like to only hide every 4, 5, & 7th line underneath the hyperlinked name. A full list could have 150 employees on one sheet.
I have numerous tabs in this file, but all of the tabs all summarize to the first tab called "Annual Record".
So after "Annual Record", tabs follow as "WO1", "WO2", etc...
On the tab called "WO1", I have cell AJ5, which can have 3 status selected (Data Validation List), which is either Inactive, Open, or Completed. If cell AJ5 on "WO1" says "Inactive", then I want a named range on "Annual Record" to be hidden. Right now I have the range named "WorkOrder1". This named range is essentially rows 4-7. So if it's easier to hide rows vs. a named range, then so be it. I also would like it to happen automatically (perhaps what you would refer to as a change event). When cell AJ5 is change to either "Open" or "Completed", then the rows would unhide. I'm thinking that this would be repeated for tab "WO2". If cell AJ5 says "Inactive", then rows 8-11 on the tab "Annual Record" or the named range "WorkOrder2" would be hidden. And again, if the cell AJ5 is change to anything other than "Inactive", then the rows or range would unhide.
I need to hide 8 rows (30-37) based on a value in cell B28.
If the value is 2 then only show rows 30 and 31 If the value is 3 then only show rows 30, 31 and 32 Then continue this up to the user puts in the value of 8 and no rows are hidden.
I have a macro that I am trying to add a feature to at the end of the code that hides blank rows. I have tried numerous methods based upon other forums, but my code does not seem to work and does not hide blank rows. In red is the section of code that I am having trouble with.
Sub Update()
Dim c As Object Dim rngA As Range Dim cc As Object Dim AA As Range
'Check every cell in the range for matching criteria.
I have been attempting to cobble together code from different forums for the past several hours to make a macro to unhide a specific number of worksheets based on a cell value, and have failed miserably thus far. Here are the specifics of what I have going on:
Microsoft Excel 2010 Workbook Name: TASERR Model - 06-26-13.xlsm
In this workbook, I have a worksheet called "Input". In cell C21 of that worksheet, workbook users will fill in a number between 1 and 50 (I have the value for that cell limited to those numbers through data validation). Based on the value in that cell (C21), I want to make a macro to unhide that specific number of hidden worksheets. I have 50 hidden worksheets which are named "Route (1)", "Route (2)", "Route (3)", etc.. If the value in cell C21 is 7, I want the worksheets named "Route (1)", "Route (2)", "Route (3)", "Route (4)", "Route (5)", "Route (6)", and "Route (7)" to unhide. If the value in cell C21 is 3, I want the worksheets named "Route (1)", "Route (2)", and "Route (3)" to unhide. I'm sure y'all can see the functionality I'm going for at this point.
The attached sheet takes information from another sheet in my work book (I am only providing the sheet I was to work with). I want all rows where the value in F is 0 to hide automatically.
Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = Sheets("Sheet2").Range("A3") Then 'A3 is the cell where your Yes/no choice is Select Case Target.Value Case "Yes": Sheets("Sheet1").Range("A7:A22").EntireRow.Hidden = True Case "No": Sheets("Sheet1").Rows("A7:A22").EntireRow.Hidden = False End Select End If End Sub
I need to hide all rows in a worksheet except the rows which contain the word which the user inputs through find (CTR+F).
The input word should be captured in a variable and this should be searched in all rows & hide all other rows in the sheet which does not contain this word.
I have a cell with today's date on it. i.e., cell A1 = today()
Then, I have rows of data with one of the columns with a date on it. I've put in an object (button), where when the user presses this button, I want all the rows of data that do not have the date on cell A1 to be hidden.
Then on the second button, when the user presses the button, to unhide all the rows that were previously hidden. To make it easier, I can simply state it to unhide all the previously hidden rows (but NOT columns - there are still hidden columns which I want it to stay hidden).
So in sum, using table below. If I have on cell A1 - today's date is 2 Mar 2013, pressing first button would hide the row with Jane Y's record. Then pressing second button would then unhide all previously hidden rows.
I've tried several approaches on how to code this, but can't figure out how to work it. I have a large spreadsheet; which dynamically changes in both column and row counts. So, I have an array of values. If any cell has a negative value, that cell text needs to be changed to red AND that row needs to remain displayed. If ALL values in that row are >=0 then the row can be hidden.
The array is actually a pivot table named QTD
For Example: If cell D5 = 5 and F5 = -3 then Row 5 should remain displayed.
If Cell D6 = 5 and F6=0 then row 6 should be hidden because all values are > 0
Then I was thinking of using the case statement to hide the rows but can't figure the syntax. I'm open to any other means of performing the taks as long as the initial array dynamically to encompass all the data.
I would like to have a VBA macro that would look over my entire spreadsheet and hide the individual row if any cell in Column A contains the text "xyzzy". The "xyzzy" can be anywhere in the cell....beginning, middle or end.
I am currently working on a workbook for work. It is a basic input output sheet. I have data from work that i copy and paste into sheet 1 and I have it arranged into sheet 2.
I am using windows 7 with Excel 2010, though needs to be compatible with 2007 using XP.
What I am trying to implement. I would have a drop down box in cell AD-4 with the current list
Select Week 1 Week 2 Week 3 Week 4
What I want to implement is a code that if I select "week 1" from drop down then it will hide the entire row if data in column B = week 2, week 3, week 4, week 6
also
If week 2 is selected then hide entire row if column b = week 3 , week 4, week 5
so basically hide the weeks that are after the current selected week.
In addition to this I would also like to hide the entire row, regardless of drop down selection if data from J, K and O all = 0 or (blank).
(the first row is the current Row labels, I have a lot of current hidden columns).
A B J K O
Supervisor Name Agent Name Needs Complete Completed w/o Turn in Needs Turn In
I have created a spreadsheet from a master using vlookup. I want to be able to hide rows which contain a null value (NA) in column B (sample attached). Is there a formula I can use which won't interfere with the vlookup or do I need to hide the rows individually? I tried filtering but that only filtered the cell and not the whole row.
I have two worksheets (Sheet1 and Sheet2). Based on meeting a condition on Sheet1 AND Sheet2, I'd like to use macros to automatically hide rows on Sheet2.
Sheet1 sample: Type Selection
[Code]...
The conditions are: If on Sheet1, a Type has the Selection "Include", then any rows on Sheet2 which have that same Type are shown (ie. not hidden).
If on Sheet1, a Type has the Selection "Exclude",then any rows on Sheet2 which have that same Type are hidden.
So in the example tables above, if ONLY Type A = Include, then on Sheet2, the rows for Sample URL1 and Sample URL2 are shown, and the others are hidden.
Also in the example tables above, if Type A = Include AND Type B = Include (and all other Types are Exclude), then on Sheet2, the rows for Sample URL1, Sample URL2 AND Sample URL3 are shown, and the others are hidden.
I'm fine with the concept of If... And... on the same worksheet, it's trying to get it to look at two separate worksheets I'm struggling with!!
I have a workbook with two worksheets, Sheet1 and Sheet 2. Based on the data entered in the 2nd column on Sheet1, I'd like a macro which hides rows for Events which do not meet the criteria entered on Sheet1.
So in the example below, on Sheet2, I only want to see rows where the Country=Scotland, AND the Location=Glasgow, AND the Start Date is greater than (or equal) the Start Date on Sheet1.
Sample of Sheet1 Country Scotland
Location Glasgow
Start Date (dd/mm/yyyy) 30/04/2013
[code]....
So in this scenario, only the FIRST row meets the 3 criteria (Scotland, Glasgow, date after 30th April), thus all the other rows should be hidden.
