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 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 RelatedI have a spreadsheet with a number of sheets two of which contain tables with many columns with a date heading, I would like a means for the user to select a range of dates and for the spreadsheet to automatically hide any columns that don't fall within this range.
View 3 Replies View Relatedworksheet 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 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 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.
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 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.
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 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.
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 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.
columns in questions are D-AC
I have a worksheet that allows you to choose 1-20... depending on the selection it should unhide specific rows but I keep getting errors...
Code that will allow you to unhide other worksheets in the same book????
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 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
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 RelatedI 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.
Like 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 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 have written a VBA procedure which is supposed to hide unneeded columns (based on the a number entered in a certain cell. This is working so far. Since I need to sum only the visible cells in a row i need to clear the contents of a range of cells if they will be hidden. Unfortunately the code i have written runs only to the ClearContents and then starts over from the beginning. If i take out the .clearcontents and put .Select for example instead, the code runs perfectly.
Here is the
Sub worksheet_calculate() 'Hide columns that are not needed
Sheet1. Unprotect
Application. ScreenUpdating = False
Dim i As Integer
Dim r As Integer
Dim s As Integer
Dim rngRange As Range
i = 2
I've come across a spreadsheet where certain rows and columns (typically top rows or left columns) are hidden; however, there's no way to unhide them (the unhide function is grayed out) and it doesn't seem to be protected or no visible macros/vba on the file.
View 3 Replies View RelatedI have a spreadsheet that has 28 columns for time entries. Typically only the first 12 columns are used, so I would like to hide the remaining 16 columns (which makes the spreadsheet much more user-friendly). It would probably be nearly impossible to teach all of them how to Unhide the remaining columns (and re-Hide), plus I would like to use the full-screen function when employees enter thier times. I would like to use a form control in the column heading so that when the employees 'check' it, it will Unhide and then re-Hide the columns. Any way to do this? Seems like a VB thing to me (out of my league, but would be happy to add one in!).
View 2 Replies View RelatedI want to create two buttons.. one name HIDE and the other UNHIDE.
What I need hide button to do when i click it is hide the 4 columns to the right (not always going to be columns b:e)
For the unhide button unhide the 4 columns to the right (not always going to be columns b:e)
I once saw an excel sheet where I could hide or unhide a section by some + and - signes above the column-letters.... I have searched for this but I only get the ordinary hide/unhide solutions.
View 4 Replies View RelatedI need to filter out rows, based on a specific value in column A, (documents on file for the clients), then check column C (last name) and D (client first name), which can have the same client listed multiple times, based on how many different documents are on file ......
View 12 Replies View RelatedI'm looking to make a simple button that would hide a given range of columns.
This is the simplest I could find:
VB:
Sub button1()
Columns("AD:AE").EntireColumn.Hidden = Not Columns("AD:AE").EntireColumn.Hidden
End Sub
Although this works nicely, there was another way to do it (looks more ergonomic and doesn't take up spreadsheet space). Here's a screenshot of what I mean: ColumnHide.gif