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".
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'm trying to create a code that when run hides a selection of columns and defines the minimum and interval value for a chart on the active worksheet. My attempt is assigned to the 'update skills' button at the top of the "GRAPH" worksheet of the attached workbook.
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.
I have an activeX combo-box that selects from different pieces of equipment that we supply. Based on that selection, I require ranges from the same page that the combo-box is on to either hide or unhide. Also, I require different tabs to become visible or hidden based on that same selection. So far so good - I have code that does this, and it appears to work without glitch.
Where the problem arises, is in one of the ranges that is unhidden when a particular piece of equipment is selected there is another combo-box that I would like to use (the number of said pieces of equipment to supply) to further hide/unhide additional ranges on the same page, and also hide/unhide certain tabs as well.
When I make a selection from combo-box 1, all works as planned, but when I change the state of combo-box 2, even with no associated coding referring to it, I cannot change combo-box 1 again without getting Error 1004 "Unable to get the Hidden property of the range class".
None of the sheets in the workbook are protected.
I would sincerely appreciate any help/code that could circumvent this error.
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.
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 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.
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 a spreadsheet that I'm trying to hide specific columns. In Row 6 I have the day of the week (Sun thru Sat) and Row 7 with the respective date starting in column F to column ZZ.
I would like to hide all columns with Saturday and Sunday in row 6. I tried using VBA but I'm new to coding and can't figure out the correct syntax.
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?
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.
I'm trying to hide columns based on information in another column. If that column contains "A" or "B", hide columns "U" through "W". If it contains "A", "B", "C", or "D", hide columns "V" through "W". Etc, etc., so on and so forth...
I've snipped the code I found earlier and modified it to (what I thought) was correct for my application, but it doesn't seem to do anything. (btw, should I get an error if it doesn't run correctly?)
Here is the code as modified:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "P:P" Then
If LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Then
Columns("U:W").EntireColumn.Hidden = True
ElseIf LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Or LCase(Target.Value) = "C" Or LCase(Target.Value) = "D" Then
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.
I would like to write a macro that automatically hides columns of data based on the value of a cell (I2) with a picklist. Cell I2's picklist is monthly values (formatted as Jan-10 though Dec-10 but real values are 1/1/2010 through 12/1/2010). I have a range that contains work week end date values (1/8/2010 to 12/31/2010) in L6:BK6. I would like to have the macro hide columns that are less than date value chosen in I2.....
I'm referencing cells A25 and A26 as the range of columns I would like to hide in Sheet 2. I equated the cells in Sheet 1 to columns in Sheet 2. Unfortunately, the result was columns being hidden from column 1 to column x instead. I would greatly appreciate if anyone can kindly correct my macro. Also, may I ask how I can distribute the columns evenly after hiding/unhiding?
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'
I have this model I created where I have two tabs. One tab is an input tab using validation and drop down menus and the other is a display tab. I simply want to hide certain QTRs based on the value of one of the drop down menu results. I tried writing the VBA code below but am a novice when it comes to code. Can somebody please help me fix the below code so that it works properly.
Public Sub hide() If Worksheets("input").Range("b14") = "Q1" Then Worksheets("Group P&L").Columns(c, d, e, h, i, j, m, n, o, r, s, t, w, x, y).Hidden = True ElseIf Worksheets("input").Range("b14") = "Q2" Then Worksheets("Group P&L").Columns(c, d, h, i, m, n, r, s, w, x).Hidden = True ElseIf Worksheets("input").Range("b14") = "Q3" Then Worksheets("Group P&L").Columns(c, h, m, r, w).Hidden = True ElseIf Worksheets("input").Range("b14") = "Q4" Then Worksheets("Group P&L").Columns.Hidden = False End If End Sub
I'm using the below macros to hide or unhide rows based off of a value in column A. Column A contains an If statement that looks for a value in column F, and depending on whether the corresponding cell in column F contains a value the if statement returns either True or NO-IF(F>0,True,"No).
It currently takes quite a bit of time to run this macro. Given the specs on this machine, I'm convinced that there is probably some loop or inefficiency in my code.
Sub Button1_Click() Dim cell As Range For Each cell In Range("a59:A1472")
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 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.
