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
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 have draft VBA code as follows:
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
[Code] ......
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 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
[Code]...
If I have a value in A1 and run a macro [button], I want to have it hide a number of rows.
Each number is one column.
eg if A1 = 2 then B:C are visible, D:IV Hidden
if A1 = 3 then B:D are visible, E:IV Hidden
if A1 = 4 then B:E are visible, F:IV Hidden
up to 200 columns.
I tried it as a select case, but it is limited to just 22 cases which obviously not enough.
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.
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 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.
View 1 Replies View RelatedLike to create a macro (button) to hide / unhide rows based on a cell value
Example
A1=No then hide roa A
C1=No then hide row c
I have a single button I want to use to call a macro to:
1.Hide columns C:AZ if they arent already hidden
2.Unhide columns C:AZ if they are already hidden.
I want to set up a macro that will hide and unhide columns.
View 9 Replies View RelatedI'd like a macro that can hide/unhide certain columns. At the moment, the columns I want to hide/unhide are F, I, M, P, U and Y.
View 4 Replies View RelatedI need hide/show some column by using Macro Button. I have attached the excel sheet( name VBA testing.xls). I need to hide column K,L,N,O & visible column G,H by clicking button "Plan A".Similarly i need to hide the column G,H,N,O & unhide the column K,L by clicking the button "Plant 2. Similarly by clicking the Button "Plant 3", hiding the column G,H,K,L are needed whereas column N,O will be unhide.
View 7 Replies View RelatedI 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.
View 8 Replies View RelatedNeed a macro for each button.
The sheet will be protected with a password (in the future, users will have varied access privileges).
Column A is designated as the "Button" Column.
There are 5 buttons here. Each representing the area on the sheet that needs to be viewed. Once the button is pressed, it takes you to that section of the sheet. At this time, I have designated each column area as:
a-z
aa-az
ba-bz
ca-cz
da-dz
I have tried this formula with opening tabs, but this won't work.
I am building a sheet to display deadlines (rows) by project (columns). I would like to have it automatically hide the rows and columns based on the date of the deadline. If the deadline is today or 1 week from today the associated rows and columns should be visible, otherwise, I would like to hide them.
My table is A1:N9, with A1 being a blank, row names A2:A9, and column names B1:N1.
I am a newbie to Excel. I really appreciate if someone could help me here and this is very urgent as I have a project going on.
I have a cell say B3, which is a dropdown list I created using Data Validation. It only lists down Yes or No.
What I need is when you click on Yes in B3, i need columns C to I to unhide. If answer is No, column C to I should be hidden. By default, the columns will be hidden.
Can you please help? I tried many times but not successful. Appreciate if someone will be able to walk me through details. If you want to see the sample spreadsheet, I can sent it to you by email. I tried Data> Outline group but they are not happy with this.
I'm trying to record a macro which will hide and unhide columns K:P of data, but I only want one button. I know how to do this to produce one button for hiding and another for unhiding...but I want one combined button.
How to use vba, how I do this via the macro recorder?
I am hoping to create a drop down list of months in one sheet, and when I select a certain month, columns in about 10 other worksheets in the same workbook will either hide or unhide columns...
The spreadsheet is laid out with columns (C-N) for each month in the year, for actuals, then columns for budget and budget variance (O-P), then YTD Actual, YTD Budget and YTD Variance. When I select September, for example, I want October-December to hide, and leave Jan-Sep unhidden, while keeping the budget, YTD and variance columns.
Is there a VBA code that can achieve this?
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 am looking or a code, (Or formula if one exists) to hide or un-hide columns depending on what is entered into a cell. I have attached an example to help clarify what i mean. Basically i want:
If A2 is empty, hide columns C, D, E & F
If A2 = Apple, Unhide columns C & D, but keep E & F hidden
If A2 then becomes Banana, rehide C & D and unhide D&F
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.
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.
View 9 Replies View RelatedI 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
#2:
To UNHIDE the rows that Macro #1 Hid.
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.
View 6 Replies View RelatedI 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 have a calculated cell (D13) which can either be (All), Grade or Rate.
If D13 = (All), I would like Rows 19:52 to be hidden
If D13 = Grade, Only Rows 19:20 to be hidden
If D13 = Rate, Only Rows 22:52 to be hidden