I have a worksheet that contains 10 columns of data. In row 2 I have a formula that will display the result "x" if the data in that column should NOT be hidden, and for all columns of data without an "x" in row 2 I would like to hide the entire column.
Therefore, I am looking for a macro that looks across the range of cells E2:N2 and if there is not an "x" in the cell, then hide that column...
To make things a little more complicated, the value in row 2 will change when other values are amended on other worksheets that feed into this one and I will need the macro to 'unhide' the column as soon an "x" appears in row 2
I would like to create a macro/vb code that will unhide a worksheet if cell value is greater than zero, or hide the worksheet if the cell value is zero.
I'm not too familiar with VB code and was wondering if this is possible? I have my doubts, since it means that the code must constantly be running in an endless loop, checking to see if the value has changed?
I want to hide and unhide columns based on a cell's value.
If D6 of the ‘Summary’ Worksheet Is <> to ‘Test1’ and <> ‘Test2’ THEN Hide columns D:K of the ‘Charts – Source Data’ Worksheet OTHERWISE Unhide columns D:K
I am trying to create a macro that will work on my master summary sheet. The sheet pulls information from other worksheets. I would like to have a macro that will look at range E7:E356. If the value is 0 then hide, but if the value changes then unhide the row. I have tried autofilter but it will not update automatically.
I need to do: Based on whether a cell returns "Export" from a vlookup function, I need a macro to run, hiding some cells and unhiding others at the same time. Also, if possible, if the cell's value changes from "Export" to something else, I need it to revert back to the original hidden/visible rows.
I'm using column A as a reference. My data starts in cell A3.
Whenever theres a zero in any cell in column A I would like that row to hide. If the row was hidden and the value changes to any number greater than zero I need the row to unhide.
I can record two macros for the hide/unhide part of this but I dont understand how to run them based on cell value.
worksheet I am working on at the moment, basically if row 5 has a 0 displayed I want that column to hide, but if row 5 has text of any value displayed I want it to unhide, the range is E5 to BA5 across.
I need to write a macro that will hide and unhide columns based on a cells value. I know how to write it to hide and unhide rows, but i can figure it out for columns.
For the rows I am using the following:
Sub HideRowsSavings() Dim LR As Long, i As Long Application.ScreenUpdating = False With Sheets("Savings #4") LR = .Range("A" & Rows.Count).End(xlUp).Row
I'm trying to find a way to hide rows based on a condition in one cell.
Basically I have a pull-down list with Yes or No in it. I want to hide rows 52 through 57, if No is selected, and make the rows reappear if Yes is selected.
I have a workbook which has roughly 50 sheets. What I'm trying to do is automatically hide/unhide sheets based on the cell values in the first sheet. So in sheet1 cell A1 i would a value of FALSE which would trigger sheets1, 2, & 3 to hide, when that value changes to TRUE then those same sheets would unhide. I need to replicate that for the 10 corresponding sets of sheets, but for each grouping of sheets a different cell in sheet1 would be the trigger, cell A2 = sheets 4 - 10, cell A3 = sheets 11 - 20, etc.
I realize there are many Hide/Unhide requests. Although, believe me when I state that I have checked into the matter, and tried to solve it on my own.
If any of you fine forum dwellers could point me in the right direction, I would greatly appreciate it. I am trying to achieve the following:
Two Macros: #1: To hide rows(entire spreadsheet) based on cell value in Column C (value is '0') Here is a code (from this forum) that seems to be the least complex/confusing. I don't understand the "AC2" value. Would it not be A2? Or does it signify a certain range?
Sub HideRows() With Sheets("Store Snapshot") If Range("AC2").Value = 2 Then Rows("13:15").EntireRow.Hidden = True If Range("AC2").Value = 1 Then Rows("13:15").EntireRow.Hidden = False End With End Sub
I've attached one speadsheet :- "VBA.xls". The following conditions are to be done:- 1. When D2=1, Column "F","G" are visible & Column "I","J","L","M" are hidden. 2. When D2=2, Column "I","J" are visible & Column "F","G","L","M" are hidden. 3. When D2=3, Column "L","M" are visible & column "F","G","I","J" are hidden.
I have a sheet called "Summary", there is a cell, AR8, of that sheet that is linked to other sheets. If AR8 of "Summary" has a value other than 0 I would like it to unhide. But if the value goes back to 0 again I would like it to hide itself again.
I have the following code which works perfectly. It needs to hide rows 3:60 based on the value in K2.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("K2")) Is Nothing Then If Not IsNumeric(Range("K2").Value) Or Range("K2").Value = "" Then Range("A3:A60").EntireRow.Hidden = True Else Range("A3:A60").EntireRow.Hidden = False End If End If End Sub
The problem I am experiencing is that I want to use a formula in K2. K2 must get its value from a cell on a different sheet.
If I manually delete the value in K2 the rows hide and if I manually enter a 1 in K2 the rows unhide, however I want to enter a formula in K2 that will result in either a 1 or blank cell.
I am trying to hide/unhide columns based on what is return to cell C2 from this lookup (=VLOOKUP(B2,GM!E2:M129,9,FALSE)). It can only return 3,6 or 9
If it returns 3 I want to hide columns I:P If it returns 12 I want to hide columns E:L If it returns 6 I want to hide E:H and M:P
I have been trying to work with this code which I found on this forum but I haven't been able to get it working fully. It only works when C2 is entered rather than calculated and I can't get it working for the M:P part of the third option.
Code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim Changed As Range Set Changed = Range("C2") If Not Intersect(Target, Changed) Is Nothing Then Range("A:Z").EntireColumn.Hidden = False
need to create a macro that will hide/unhide rows. I have a list of items that need to be completed and under each item is another list that explains how to complete the items on the first list.
Let’s say for example, How to bake a cake
I want rows 2 – 6 to be hidden when the file is opened and then unhide when A1 is clicked. Then hide again when A1 is clicked again.
I have a huge list of items that need to work in this format.
The other thing I need is when the document is printed all of the hidden items are printed.
I have a master sheet where users can change the name of 20 different sheets in the workbook by changing a cell value on the master sheet. Here is the code:
Code: Private Sub Worksheet_Change(ByVal Target As Range) Dim rCell As Range If Target.Cells.Count > 1 Then Exit Sub
[Code]....
The next step which I am having issues with is hiding the sheets. I want all sheets in the workbook to be hidden except for the master. I only want the other sheets to be visble if the user inputs a name on the master sheet.
So if the cell contents on the master sheet, say "B9", is blank, the sheet in the workbook that corresponds to that cell will remain hidden. If the user inputs anything, say "Sheet1", in cell "B9" on the master sheet, I want that sheet to become unhidden and to be named "Sheet1"
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 13 Then Exit Sub If InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then Columns("N").Hidden = True End If End Sub
but I have a lot of columns that I need to perform as above and I have put the code together as below
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next
If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then.................
Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.
Trying to hide a rows based on value being inserted to A1 = 0 (if value is >0, then unhide), but it's not working.
Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1").Value = 0 Then Rows("33:42").EntireRow.Hidden = True Else Rows("33:42").EntireRow.Hidden = False End If
Attached is a portion of the file I will be working with, just to illustrate what I would like to do.
I have a macro that copies and pastes data - it uses these Tasks as templates. The macro copies these rows for each task that is required under the headings (Task #1 - Task#?). What I would like is to hide the rows under Station Maintenance Task #0 and it's 6 rows that are associated with it, as well as Task #0 under CMS and the 12 rows associated with it. In order for the macro I already have work properly is that these would be unhidden for the macro to run and then re-hidden afterward.
I searched on the forum for this topic, and found hide/unhide where you would put in the beginning row and ending row - however since there will be numberous rows added in, this type of defining rows would not work.
I made this simple little macro to hide/unhide a row on a different sheet based on a check box. The row hides fine. The problem I have is unhide. I uncheck the box, but the row stays hidden. What am I missing to make this little gem come to life? This is for tracking popcorn sales for scouting and I'm trying to make it as easy to use as possible.
Sub Patrol1_Scout1() If True Then Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = True End If
If False Then Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = False End If End Sub
I am very new to VBA. I need to have a code that will hide/unhide cells based on a date range.
Cell A1 contains the formula for todays date, based on that answer, I want to hide/unhide columns based on a weekly date range.
Ex.:
Today is 03/18/2014 I want excel to say If A1(Today) is greater than 03/14/2014 and less than or equal to 03/21/2014 then unhide column E and hide the rest.
Then let's say Today is 03/22/2014. I want excel to say If A1(Today) is greater than 03/21/2014 and less than or equal to 03/28/2014 then unhide column F and hide the rest.
I need to show hidden columns based on the date I entered. For example, if I entered "1/1/1990" on a1 as the starting date and "4/30/1990" on b1 as the ending date. I want Excel to show the columns that are covered by the date, thus it shows Jan, Feb, March and April. How do I do that? Here's an example attachment. In here Sheet 1 is the starting point, the highlighted cells is where I enter the date. the Result sheet shows what I want Excel to show me when I have a date entered.
Develop a macro that will enable me to hide or unhide worksheets.
Basically, I have a few worksheets in my workbook and I want to have a page at the front with two buttons. One for Unhide sheet and the other for Hide sheet. Once activated, I want it to give me a list of worksheets that I can click to hide or unhide (depending on the button i click).
Macro 1- hides Sheet1 and unhides Sheet2 Macro 2- Hides Sheet2 and unhides Sheet1
I used the macro recorder to attempt to make this work but am running into a problem if Macro1 is run two times consecutively. In this situation the macro displays a debugging error b/c Sheet1 is hidden. Is there a way to get around this...possibly using an if then statement?