I 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 RepliesI 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 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 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'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'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
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 RelatedThe code below if I run once Hide the Range("H:FV,GG:IV") and shows the Range("A:G, FW:GF") And if I run it again Unhide the Range("H:FV,GG:IV") And Show all columns
Code:
Sub Hide_Unhide()Range("H:FV,GG:IV").Select
If Selection.EntireColumn.Hidden = True Then
Range("H:FV,GG:IV").EntireColumn.Hidden = False
Else
Range("H:FV,GG:IV").Select
If Selection.EntireColumn.Hidden = False Then
Range("H:FV,GG:IV").EntireColumn.Hidden = True
End If
End If
Range("A1").Select
End Sub
Above code is working with 2 cases now is it possible to add 3rd case Hide Or Unhide Range("H:GG,GI:HJ,HO:IV") in the same code, and shows the Range("A:G, GH, HK:HN")
Resume: my request
Step1-if I run macro First time it must Hide Range("H:FV,GG:IV") and shows the Range("A:G, FW:GF")
Step2-If I run macro Second time it must Hide Range("H:GG,GI:HJ,HO:IV") and shows the Range("A:G, GH, HK:HN")
Step3-if I run macro third time it must show all columns
And repeat same cycle all time Step 1 to 2, Step2 to 3 and Step3 to 1
I have a worksheet with a fair amount of data. It is split into two parts: the main part is a table with data in columns D to AR while the other part is simply a list with checkboxes against each item. I have set things up so that when a checkbox is ticked against an item in the list, then a 2 appears in row 2 above the relevant column in the main table; if the checkbox is unticked then a 2 appears above the relevant column.
What I seek is a macro that will hide all columns that do not have the associated item ticked; in other words, I want columns to be hidden if there is a 1 in the relevant cell in Row 2 (and visible if there is a 2 there).
I have used the following code (obtained from this forum), but it doesn't work as expected. When I select the items, the cells in Row 2 react as expected, but the hiding and unhiding only occurs when I go to another Worksheet and then return to the Worksheet where the data is. Obviously I want the macro to work immediately I tick or untick a checkbox. What is wrong with the code I have used?
Private Sub Worksheet_Activate()
Call HideCols
End SubSub
[Code]....
I'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 have a range of columns ( J:AH) which i have to hide from the user interface.In order to unhide, the editor must enter a password. He/ She can only enter the password thrice. After 3 times, if it is still wrong, the whole workbook must be closed. Is this possible? If not, I'll settle for something less complicated.
View 9 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 have a speadsheet that has 27 columns for time entries, however employees seldom need to use more than the first 12 columns. I need to protect the sheet but would like the employees to be able to Unhide the columns when they need to use them, then reHide them. I can't find this as an option in either 2003 or 2007.
View 2 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'm looking for a macro to hide and unhide columns..
Book1 is where I want my macro to sit with 1 cell showing the period number 1-12.
Book2 is where my data is dispalayed (I've got about 44 of these to update)
B4:M4 in book 2 (and all data sheets) has the period number in it.
I would like to be able to update the period in book1, run the macro, and end up with book 2 showing column A, the current period column, column N.
I'm fairly new to writting macro's and think I'll be able to addapt the formula to work for 44 sheets once I have it working for 1!
Need 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 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 am trying to hide or unhide columns E:BL depending if the the cell value in row 54 contains a value (Note cells in row 54 contain formulas). I have written the following code but get a runtime error on the else statement.
Sub UpdateCashflow()
Application. ScreenUpdating = False
ActiveSheet. Unprotect Password:="LD"
ActiveSheet.Select
Dim c As Integer
For c = 5 To 64
If Cells(54, c) = "" Then
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = True
Else
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = False
End If
Next c
ActiveSheet.Protect Password:="LD", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.EnableEvents = True
'
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.
When my spreadsheet opens it automatically protects the sheets using the following
With Sheet1
.Protect Password:=SpreadsheetPassword, UserInterfaceOnly:=True
.EnableOutlining = True
End With
and I use the following to hide the column if the toggle button on my form is 'true'. The problem is it gives an error if the sheet is protected. it worksfine without protection.
If ToggleButton1.Value = True Then
Range("column_calories").EntireColumn.Hidden = True
Else
Range("column_calories").EntireColumn.Hidden = False
End If
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 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'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 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]...
Need the VBA script to hide/unhide specific columns within a worksheet.
View 9 Replies View RelatedIf 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.