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 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'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.
I 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!).
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.
I'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.
The 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
I 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.
I 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.
I 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.
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.
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
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 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.
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
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.
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 trying to create a very large spreadsheet and i have got everything organized, but for easy viewing i want to have a toggle button that hides and un-hides certain sets of the columns (ie. Press down and it hides columns "B-F", "H-J", and "M-O".... then on depress it shows all the information again)
I have got some of the coding down, but am only able to get it to hide one set at a time, not all the sets. Can anyone help me out and let me know how i tell it to run multiple commands from the one press? here is the code i have already: ...
