Hiding / Unhiding Rows Based On Cell Input

Jul 2, 2009

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

ADVERTISEMENT

Hiding And Unhiding Rows In Multiple Sheets Based On Specific Cell Value With VBA

Jan 4, 2013

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.

View 9 Replies View Related

Hiding/Unhiding Rows

Feb 8, 2010

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.

View 5 Replies View Related

VBA For Hiding/unhiding Rows

Sep 27, 2007

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.

View 9 Replies View Related

Hiding And Unhiding Rows With Checkbox

Jun 17, 2014

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.

View 2 Replies View Related

Hiding/unhiding Specific Rows Containing Zero

Jul 23, 2008

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.

Sound so easy ... but sadly I can't do it.

View 14 Replies View Related

Conditionally Hiding / Unhiding Rows In One Worksheet By Referencing Cell In Different Worksheet

May 3, 2013

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.

View 5 Replies View Related

Toggling (hiding/unhiding) Blank Rows Across Sheets

May 5, 2006

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.

View 4 Replies View Related

Code For Hiding Or Unhiding Columns Based On Row Values

Feb 2, 2007

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".

View 2 Replies View Related

Hiding / Unhiding Columns

May 20, 2007

I have a spreadsheet that i would like to hide all columns from B:M and to select the column/s I want to unhide.

I would like VBA cose to do this or to set up a combo box that will allow me to hide/unhide specific columns....

View 9 Replies View Related

Hiding And Unhiding Two Sets Of Worksheets

Oct 11, 2008

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

View 11 Replies View Related

Hiding And Unhiding Sheets Using A Macro

Apr 27, 2012

I would like to be able to hide the tab i am in and unhide another tab at the click of the button,

I have tried recording the macro myself and it works up to a point,

I want it to finish on the tab i have just unhidden but for some reason it doesn't seem to do that (even though that's the way i recorded it)

View 4 Replies View Related

Hyperlink VBA Hiding / Unhiding Lines?

Feb 27, 2013

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.

View 9 Replies View Related

Hiding & Unhiding Tools / Macros

Jan 26, 2007

I want to hide and unhide tool / macros menu so that users don't run the macros in the workbooks.

View 9 Replies View Related

Hiding Rows On One Tab Based On Value Of Cell In Another Tab?

Dec 27, 2012

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.

View 9 Replies View Related

Hiding Rows Based On Value In Cell

Mar 5, 2009

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.

View 9 Replies View Related

Hiding Rows Based Upon A Cell Value

Sep 7, 2009

I'm trying to come up with some code that will hide a number of rows within the range row 7 to row 15 based upon the value of cell G1.

e.g if G1 is 1 rows 8-15 are hidden, if it is 2, rows 9-15 are hidden i.e the number of rows visible within that range equals the value of G1.

View 9 Replies View Related

Hiding Rows Based On Cell Value Not Working

Jun 26, 2014

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.

[Code] ...........

View 3 Replies View Related

Excel 2010 :: Unhiding Specific Number Of Worksheets Based On Cell Value

Jun 26, 2013

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.

View 8 Replies View Related

Hiding Rows Based On Value

Apr 22, 2014

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.

Attached File : Book2.xlsx‎

View 3 Replies View Related

Hiding Rows Based On Value?

Mar 27, 2012

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

View 5 Replies View Related

Hiding Rows Based On Criteria

Dec 22, 2011

I am stuck with a task.

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.

View 2 Replies View Related

Hiding Rows Based On Condition

Feb 11, 2013

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.

DATE
NAME
DEPT

1 Mar 2013
John X
Accounting

2 Mar 2013
Jane Y
Operations

3 Mar 2013
Joe Z
Marketing

How can I accomplish this?

View 6 Replies View Related

Hiding Rows Based On Multiple Values

Aug 7, 2012

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.

View 7 Replies View Related

Hiding Rows Based On Text Value In Column

Feb 2, 2010

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.

View 9 Replies View Related

Excel 2010 :: Hiding Rows Based On Dropdown

Mar 6, 2013

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

[Code] ........

View 7 Replies View Related

Hiding Rows In VLOOKUP Sheet Based On Null Values

Dec 30, 2013

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.

test sheet.xlsx

View 3 Replies View Related

Excel 2003 :: Hiding Rows Based On Conditions On Two Worksheets?

Jan 4, 2013

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!!

View 3 Replies View Related

Excel 2003 :: VBA / Hiding Rows Based On Three Conditions On Two Different Worksheets

Jan 14, 2013

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.

View 6 Replies View Related

Hiding Rows With A Cell Containing 0

Oct 25, 2006

a bit of code so that when I open a sheet (which takes data from another sheet) it hides rows which contain a zero in a certain cell?

View 10 Replies View Related

Unhiding Text Based On Result?

May 8, 2014

see the sheet attached. To put into context, I am a teacher and want students to work on tasks, and have the answers to those tasks available to them but hidden, until a give them a password that unhides it.

On the attached document, Lesson1! K35:Q41 i have some answers to the task beside it in B35:H40. I want to hide these answers in some way (without affecting the columns as there is data above) and then when I want students to see the answer I give it to them and they enter it into L43 in this example. At this point the answers are revealed and stay revealed for revision purposes.

This will be repeated using different passwords for different tasks throughout the workbook e.g. L52 will be a different password revealing the answer in K48.

View 6 Replies View Related

Hiding Row Based On Cell Content?

Jul 9, 2014

I am trying to search the cells in Column A around rows 54 to 77. And if the cell says "Yes" it should hide the row. I think I'm on the right track, but can't seem to get it work.

[Code] .....

View 6 Replies View Related

Hiding Columns Based On Cell Value

Aug 7, 2011

I am trying to hide columns where the Value in the cells on row 9 is "", that cell being populated with a formula where the result is "". However I am getting the Compile Error Message 'Next without For'. Any clues?

Sub GraphC()

Dim a As Integer
Dim ColumnVar As Variant

ColumnVar = Array("B", "C", "D", "E", "F", "G", "H", "I", "J", "K")

[Code] .........

View 8 Replies View Related

Hiding CheckBox Based On Cell Value

Mar 20, 2012

Here is what I have so far (debugger highlights my CheckBoxLD.Visable arguments)

Yes, this is an activeX checkbox

Code:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Worksheets("Dosing").Range("BM21").Value = 0 Then
CheckBoxLD.visable = False
Else: CheckBoxLD.visable = True
End If
End Sub

View 4 Replies View Related

Moving Rows Based On Input Of Date?

Jun 17, 2014

I am a production manager for a homebuilder and I am trying to create an Excel spreadsheet that will allow me to input a date in a column and have a second column dependant on the date and move as I change the date.

Since no work is done on weekends, I'd like the output column to skip weekends and move the input to the next available weekday. I have conditionally formatted the date to be a red fill as a visual cue but I'm not sure where to go from here.

View 7 Replies View Related

Delete Rows Based On User Input

Mar 6, 2009

1) Allow user input of a number between 1 and 999

2) Search column three and delete any rows that don't match this number.

I have some code that would delete any rows within a certain column, based on predefined criteria and i've tried modifying it to suit my needs.

View 7 Replies View Related

Show Rows Based On Value Entered In Input Box?

Mar 7, 2014

I would like to present the user with an input box asking him to enter a numerical value greater than 0.

Then I would like the code starting with row 10 and consult the vaues in column O, and only display the rows that have number in it less than or equal to the number in the input box. I was hoping it would involve a filter.

I would also need a macro to display all of the rows again.

If this is too hard, how about copying the results to a new worksheet along with the labels in row 9?

View 2 Replies View Related

Hiding Sheets Based On Value In Dropdown Cell

Mar 21, 2007

I have a workbook that has about 30 different sheets with names titled "Joe", "Jane", "Paul", etc.

I have a Cell (B12) that has been formatted as a dropdown menu with about a dozen different options such as "Audit A" , "Audit B" or "Other" etc. What i'd like to do is hide or display certain sheets based on what the value is in cell B12.

if cell B12 says any of the following "Audit A" or "Audit B" then it would hide certain sheets. If the value in B12 says "Other" or something other than "Audit A" or "Audit B" then it wouldn't hide anything at all. I tried searching and couldn't find anything like this although it may have been answered before.

View 14 Replies View Related

VBA - Hiding Columns Based On Cell Values

Mar 20, 2014

the support this board has given me as I learn VBA. I have three columns - Q, R, and S. I only need to see columns R and S if the cell values don't equal those in column Q or each other. So if I have cell values like the ones listed in the example below, then I don't need to see columns R and S.

Q Header
R Header
S Header
50
50
50

[Code]....

View 4 Replies View Related

Hiding Columns Based On Cell Value (macro)

Dec 1, 2009

I have an elaborate spreadsheet that I have made for my company. I will give a little background how I have it set up:
E6 contains a drop down menu of all products that we sell, and line items are populated based on the selection here. In columns N and O, I have a "Business Partner Costs" table that calculates a specific discount % based on the business partner.

Some of our products are sold to BPs with no discount. For these specific products, I am trying to get the BP Costs table to disappear, or hide.

Basically, I want something like: "=if(or(e6=ae2,e6=ae7,e6=ae12,e6=ae13,etc),hide columns N&O,show columns N&O)" in macro format.

View 9 Replies View Related

Hiding Columns In The Worksheet Based On Cell Value

Oct 20, 2006

I have a worksheet that has >10 rows of data and over 150 columns. The values in the cells for individual row include NA, NE, D, A and S. Each row will have one or all of these values in one of the cells. Here is the question:

How do I hide columns containing 'NA' in the cell for a particular row, when only that row is selected by clicking on any cell in row 'A'? For example: If my row 3 cell values for column A, J, R, and X are 'NA' I want to hide the column containing 'NA' only and display everything else? And only when I click on row 3 column 'A'

View 2 Replies View Related

ADVERTISEMENT