Hiding / Unhiding Rows Based On Cell Input
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
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Hiding/Unhiding Rows
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 Replies!
View Related
VBA For Hiding/unhiding Rows
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 Replies!
View Related
Hiding/unhiding Specific Rows Containing Zero
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 Replies!
View Related
Toggling (hiding/unhiding) Blank Rows Across Sheets
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 Replies!
View Related
Code For Hiding Or Unhiding Columns Based On Row Values
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 Replies!
View Related
Hiding Rows Based Upon A Cell Value
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 Replies!
View Related
Hiding Rows Based On Value In Cell
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 Replies!
View Related
Hiding / Unhiding Columns
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 Replies!
View Related
Hiding And Unhiding Two Sets Of Worksheets
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 Replies!
View Related
Delete Rows Based On User Input
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 Replies!
View Related
Hiding Columns Based On Cell Value (macro)
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 Replies!
View Related
Hiding Sheets Based On Value In Dropdown Cell
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 Replies!
View Related
Hiding Columns In The Worksheet Based On Cell Value
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 Replies!
View Related
Code To Delete Rows Based On User Input
I am using the following piece of code to delete unwanted rows from a worksheet: Sub DeleteDates() FinalRow = Cells(65536, 3).End(xlUp).Row For i = FinalRow To 1 Step -1 If Cells(i, 3).Value Like "*2007*" Cells(i, 1).EntireRow.Delete End If Next i End Sub What I am working with is a sheet of about 5000 customer appointments going back to the beginning of 2006. A have peiced together a lot of code to format it exactly as I want and to create a pivot table of what is remaining after the rubbish has been deleted. However I am finding that I need to keep amending and re-running this bit of code, depending on the date range I want to look at. For example the code above will delete all appointments made in 2007. My question is: is there a way that I could incorporate an input box, whereby excel asks the user for a start date and an end date and deletes any rows outwith that range? Some important info: All of the dates are in column C, in the format mmm yyyy I would only need to narrow down to a month - so for example mar 2008 - jun 2008
View Replies!
View Related
Change Result Cell Format Based On Input Cell Value
Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
View Replies!
View Related
Formula For Cell Result Based On Other Cell Input
I'm trying to work out the way of having a cell give a result based on what has been input into two other cells. Basically I'm doing up a risk management spreadsheet. I need to have in one cell the LIKELIHOOD of an incident, in the second cell, the CONSEQUENCES of it happening and ending up with a RISK MANAGEMENT ASSESSMENT in the third cell.
View Replies!
View Related
Cell Values Based On Input Of Another Cell And Sheet
I want to tie the values of some cells on Sheet 2 to what's put in into corresponding cells on Sheet 1. Example If Cell B3 on Sheet 1 has the string "B off" put in, Sheet 2 should automatically get "R1" in cell B12, "B off" in cell C12 and "ID123456" in cell D12. If cell B3 on Sheet 1 is empty, B12:D12 should be blank.
View Replies!
View Related
Restrict Cell Input Based On Text Of Another Cell
I have two coloumns on a worksheet. In the first column I have data validation drop down list. I want to be able to: if the first column "Other" is chosen then the next cell will allow text for description, if anything other is chosen in the first drop down list cell then I want the next ( text for descritption ) cell to be resticted.
View Replies!
View Related
Highlight Part Of Cell Based On Input
I want to track winning football teams by highlighting the victor. So lets say I have a cell that contains Raiders @ Niners and another cell containing "home" or "away" indicating the winner. Based on the home/away cell, I want to either highlight Niners/Raiders.
View Replies!
View Related
Run A Macro Based On Value Input In A Cell
I am using data validation to restrict the number of Characters a user can input in a range of cells. The number of characters in this however can vary based on which selection they selected in a different cell. To put this as an example, if the user selects "hello" in cell a1, the data validation would allow 10 letters in the cell range c1:c20. If the person selects "goodbye" in cell a1, then the validation would only allow 7 characters in cell range c1:c20. I have been using the worksheet Change function, however it updates the validation regardless of what cell is changed.
View Replies!
View Related
Protect Or Unprotect Cells Based On Cell Input
I am working on my spreadsheet and was wondering if anyone knows if cells can be Protected or Unprotected based on the value of another cell? Here is what I am looking for: If cell "A16" on the "Input Sheet" equals AR then Protect Cells J16, K16, L16, M16, N16, and O16 If the value of A16 on the Input Sheet DOES NOT equal AR then leave those cells unprotected. Has anyone ever done anything like this? On my spreadsheet it would allow the user to skip the unnecessary cells as opposed to having to Tab through them. Not a big deal but definitely would be a time saver and a lot more user friendly. If you have any questions or concerns just let me know. I will wait to see what the Excel Guru's come up with.
View Replies!
View Related
Conditional Input In A Cell Based On Data In Two Different Cells And Other Values
I have lot of data in three columns like this. The first column is "Name", the second is "Comment" wherein I want the macro to write some comment, the third one is DOB. The problem is that the names in column 1 repeats many times. I want a macro to write in column B "either Old or Older or Oldest" based on the Name and DOB. Thus David with DOB 13 Sep 1982 be marked Oldest in Column B and David with DOB 25 Aug 1988 be marked Older and David with DOB 24 May 1990 be marked Old. Similarly William and Rita should be marked either old or older or oldest. As the data are enormous I do not want to refer the actual name in the programming. I want to call them using a variable in programming. NameComment DOB David 25 August 1988 David 13 September 1982 David 24 May 1990 William 24 March 1980 William 25 July 1987 William 13 August 1989 Rita 17 July 1990 Rita 24 April 1989 Rita 13 June 1988 The example file is attached
View Replies!
View Related
Hiding Rows...
I have two command buttons, and need one macro that would hide all blank rows between columns "E" and "O" and from row 9 to 42, and then another macro that would unhide all these rows again.
View Replies!
View Related
Hiding Rows When Blank
how to hide rows that are blank as a result of formulas in that row returning "". I have rows between A10 and A80 that I need to test for blank as above an automatically hide them if they are "". I need to redo this each time I view the sheet Be gentle, I am just a VBA newbie and havent quite finished reading Bill Jelens excellent VBA book purchased on this site.
View Replies!
View Related
Hiding Rows Through A Macro
i'm having trouble hiding rows through a macro. In this case, i'd rather use a macro than use autofilter.... i would like a macro that does the followings... 1. for each row in worksheet (up until row 2000), if the value in the first cell = YES, then hide row, otherwise, leave row as is...
View Replies!
View Related
Automatically Hiding Rows
in worksheet "sheet1" I have my inputs... in "sheet2" I have my outputs... my inputs could be anything from 1 to 1000 therefore my outputs would be 1000... on the output sheet, below the last line, i have a whole bunch of text and calcs that I will always need at the bottom... what i would like to happen is if I put in "sheet1" b2 a number, say 5, then "sheet 2" would hide rows 10 to 1005 or if i put in "sheet2" b2 say 925, it hides 930 to 1005... I created a macro, but it doesn't work at all... and i want it to work automatically without me pushing run macro...
View Replies!
View Related
Hiding Blank Rows
I have had great success using this code to hide and unhide unused space, but for some reason this morning it broke. Most likely it is my error so I am looking for another solution to resolve the issue. What i would like to be able to do it hide an entire row based on a specific cell being blank. maybe that is what this code is supposed to do but like i said it broke this morning. the cell in ref in the code is unchanged, but i did add formulas to some other cells in the same row. this is why i am looking for a way to do the same thing but based on a specific cell being blank vs what is occuring now.
View Replies!
View Related
Hiding Of Empty Rows
How do I go about hiding empty rows in a separate linked sheet? To be more precise, I'm working on 2 sheets now. Sheet 1 would be the input sheet and sheet 2, the output sheet. For instance, there are 10 rows of data in Sheet 1, but out of this 10 rows, I planted data only into 4 rows. How do I ensure that Sheet 2 hides the empty rows (5th - 10th)?
View Replies!
View Related
Hiding/Showing Rows With Combobox
I am trying to hide/show a static set of rows based on the value chosen within a combobox. The combobox has a list of names and one blank. The blank is selected by default, which renders the rows hidden. When a user selects a name in the combobox the rows will be shown. I got some help on this, which allowed me to hide the rows when the workbook was activated and when the worksheet was activated, but the code to show/hide the rows based on the combobox value is not working.
View Replies!
View Related
Error 424 When Hiding Rows
I have some vba code that is used to hide any rows with the cell value of "Finished". The code is below. When I run this code I get a run time error 424 - Object Required. Can you please explain or help as to why I am getting this? As a side point the data is loaded from an oracle database rather than from the sheet but every other function/code works fine.
View Replies!
View Related
Hiding The Rows While Searching Data
I am looking for some help with my data, I have this excel sheet in which i have certain abbreviations and answers to some questions which we use on our daily job. Now what i want to do is create a search box on the sheet and find the matching Abbrevation on the complete excel. The trick here is to hide every thing else when i search for some thing and see only for what i want to see in order to not to get confused. I am attaching the excel to give an idea to what i am looking for.
View Replies!
View Related
Slow Code For Hiding Rows
I have some code which looks for the word "Out" in column "L" and hides the row if it finds it. This is running from row 24 to 160 and takes about a minute to run. I've tried two different solutions, neither really quicker than the other.
View Replies!
View Related
Hiding Rows And Columns At The Same Time
I have a macro that works great to hide rows with zeros in a specific column on one sheet. It is below: Sub Hide_Rows() Dim i As Integer ActiveSheet.Unprotect Password:="Yourpassword" Application. ScreenUpdating = False Application.Calculation = xlCalculationManual For i = 9 To 408 If Sheets("Tasks and times"). Range("H" & i).Value = 0 Then Rows(i & ":" & i).EntireRow.Hidden = True End If Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True ActiveSheet.Protect Password:="Yourpassword" End Sub My problem is that now I want to adapt it to also hide columns on another sheet in the same workbook (named OBS). This is because in the first sheet rows 9 through 408 are used for data but that range is divided into 25 sets of 16 rows. Each set corresponds to a column on the other sheet starting at column C. I would like the corresponding column to hide when the first row of each set (i.e. rows 9, 25, 41, etc.) are hidden.
View Replies!
View Related
Hiding Columns Based On Criteria
I need to run this funtion from the starting column (N) to the last column ( dynamic). Sub HideUnits() Dim rngData As Range, rngHide As Range Application. ScreenUpdating = False With Sheet1 .Rows(1).Insert .Range("B1").Value = "Temp" Set rngData = .Range("N1:N" & .Cells(Rows.Count, "N").End(xlUp).Row) rngData. AutoFilter field:=1, Criteria1:="*Units" Set rngHide = rngData.SpecialCells(xlCellTypeVisible) rngData.AutoFilter rngHide.EntireRow.Hidden = True .Rows(1).Delete End With Application.ScreenUpdating = True End Sub As you might be able to tell I need to hide all the columns with a specific word in the heading. I'm pretty sure I'd need to loop this somehow, but I'm not good with VBA.
View Replies!
View Related
CountBlanks Error :: Hiding Rows Portion
I pieced together this code last night but I cannot seem to get the hide row function to work correctly. Any ideas on how I can correct the hiding rows portion? I just want the code to hide the entire row if columns K:Z are all blank. So I assumed I can make the CountBlanks=16, then hide that row if its true! Thanks to Sixth Sense and Norrie for that idea! Code: ....
View Replies!
View Related
Hiding Rows With Zeros (Multiple Columns)
I have values in column D & K. Now i want to hide the rows with Zero's if they are in both D & K. Now if there is a Value in K and a Zero in D i don't want to hide that row. Could someone please help if this is possible. I attached the code below for hiding lines according to one column:
View Replies!
View Related
|
TRACKER
