Auto Hide Columns Based On Cell Value
Mar 12, 2012
I got an excel, with a "validation list" in a certain cell.
Users can select value1, value2 or value3. based on this value, some columns will have to unhide (standard= hidden)
(only for value2 this is the case).
I used the worksheet_change event to determin the value, but so far I'm only getting it to hide...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If C24 = "value2" Then
Columns("H:O").Hidden = False
Else
Columns("H:O").Hidden = True
End If
End Sub
View 5 Replies
ADVERTISEMENT
Apr 1, 2013
I am creating a copy of a spreadsheet (table format) in a separate workbook using formulas that will update the copy as the original updates. I would like to auto hide the rows that have the value "Returned" in column G. I have columns A - G , rows 2 - 2000. The value "Returned" is the result of a simple = formula. Is there some way to accomplish this?
View 4 Replies
View Related
Jan 30, 2009
I've attached a screenshot to illustate what I am doing and a one tab version of the workbook. I've had to do it in a zip folder as they were too big individually.
I've got a table that starts at row 12 and finishes at row 217 and the users enter information into the rows over a year. The creator of the sheet has set it up so there is a "z" in the second cell of each row and as this cell is overtyped with the new information the row changes colour and is included in the selected print macro that is set up.
I want to add in a macro that changes the row height to 0 based on the "z" being present in the row above 2nd cell. So all that is showing in the table are the rows that have info in them and one blank one underneath. So everytime a new row of info is entered either a new line will reveal itself underneath or there is a control button on the sheet that the user can press to reveal a new empty line.
I don't know how to write VB, but I've found some code online that claims to do what I need, but I need it to be altered to use the presence of the "z" in the row above (2nd column) as the trigger for the rule:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Value = 1 Then
Rows(Target.Row).RowHeight = 0
Else
End If
End Sub
Or should I be starting off with minimised rows and changing it so the height increases as the "z" in the row above is overtyped?
Can this happen automatically as the z is overtyped or does the macro need to be triggered by a control button for example?
Is there a better way to do this? I don't want to get rid of all the extra empty rows and have a macro to create a new row for 2 reasons: 1.They have formulas and macros running set up by the creator that I don't want to mess with and 2. There are 52 sheets in the workbook, 1 for each week of the year and the next sheet takes the information from the previous weeks sheet so on the last sheet, number 52, it has every line that has been entered over the year from week one to week 51 carried over. If I created a new row on week2, I would have to then create that row on every sheet following week 2 and I think that would make it more complicated. I would need the macro to be able to run on any of the 52 sheets.
Private Sub Worksheet_Change(ByVal Target As Range)
Cells.RowHeight = 12.27
Range(Rows(Target.Row + 2), Rows(217)).RowHeight = 0
End Sub
View 9 Replies
View Related
Feb 21, 2010
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:
View 9 Replies
View Related
Mar 4, 2013
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] ......
View 1 Replies
View Related
Apr 3, 2008
I'm sure this is an easy one, but I have never done this before. I need to know the best way to hide (for example) Columns U:W if cell U3 is blank.
View 9 Replies
View Related
Nov 15, 2006
I need to hide columns in a spreadsheet without using the hide/unhide columns option. I found an article which suggested setting up a combobox from the forms toolbar, this is populated by an input range and linked to a cell for a numeric value. The article although using a macro to hide rows suggested this code
Private Sub ComboBox1_Change()
If ComboBox1.Value = "A" Then
ActiveSheet.Range("1:3").EntireRow.Hidden = True
ActiveSheet.Range("4:6").EntireRow.Hidden = False
ElseIf ComboBox1.Value = "B" Then
ActiveSheet.Range("1:3").EntireRow.Hidden = False
ActiveSheet.Range("4:6").EntireRow.Hidden = True
End If
End Sub
I want to amend this code so that when the number in my linked cell changes the various columns are hidden.
For example if the cell link number = 1 hide columns e to az if the cell link number = 2 hide columns d and f to az etc.
I have had varying success with amending this code to entirecolumn.Hidden and changing the Combobox1. to dropdown1. but am unable to get it to work, I also stupidly deleted the code that was partially working and am now unable to remember what I'd done.
View 9 Replies
View Related
Sep 4, 2007
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.
View 9 Replies
View Related
Jan 25, 2010
This is what I would like to do if possible: If E9 on Sheet1 is 1, then hide columns F-L on Sheet2.
View 4 Replies
View Related
Dec 7, 2011
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
View 4 Replies
View Related
Dec 4, 2013
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]...
View 1 Replies
View Related
Jul 17, 2007
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.
View 9 Replies
View Related
Jun 4, 2014
I have a workbook with two sheets, Project Overview and Projects Detailed. In Project Overview column K is different project numbers and what i need the code to do is when this number is pressed it has to go to Projects Detailed and search for this number in column AV and hide the lines that does not correspond with the number that has been double clicked.
In project overview you should only be able to press the numbers from row 9 and down and in Projects Detailed the numbers in column AV also starts in row 9. Some of the cells in column AV are empty and these should also be hidden.
View 2 Replies
View Related
Sep 17, 2008
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
View 8 Replies
View Related
Jan 10, 2009
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.
View 8 Replies
View Related
Mar 24, 2013
I have data in three columns
A B C
4.5 9.50 4.657
4.5 11.60 4.789
4.5 12.50 4.654
4.5 13.50 3.930
4.5 15.20 3.826
I need to be able to auto-populate the cell in column G based on values in columns A & B. If I type 4.5 in E2, and 13.50 in F2, G2 should auto-populate with the value from column C
E F G
4.5 13.50
View 2 Replies
View Related
May 23, 2014
Hide Columns based on Date value in cell (Worksheet Change event)Looking for a worksheet_change event macro that will automatically hide columns based on a value in cell S3. S3 is a data validation pick list of months based on the 1st day of each (e.g. 1/1/2014, 2/1/2014, 3/1/2014, etc.). I have date columns in row 6 from columns T through AQ. These values are 1/1/2014 (T6), 2/1/2104 (U6), 3/1/2014 (V6)....12/1/2015 (AQ6). I would like to hide columns that have a date in row 6 (T6:AQ6) that is LESS THAN (<) the date in S3 after the cell is value is changed.
View 2 Replies
View Related
Mar 6, 2014
I've created an excel workbook to automatically create a report for my work. All you have to do is enter the data into the "DATA" worksheet. Easy. Only problem is that someone can easily ruin it if they try to run it when the "DATA" sheet is empty. I figure that if I can get two columns hide when it's empty and that would solve my problem. I've tried several VBA codes, but they don't work. Also, everything except the "DATA" sheet is protected to prevent accidently changes. This is the latest code I've tried:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "DATA!$P$5" And Target.Value = 0 Then
ActiveSheet.Unprotect ("password")
[Code].....
View 4 Replies
View Related
Aug 5, 2008
I have an Excel workbook with multiple sheets. In one sheet, there are many columns that automatically get hidden based on cells values (=1) in another sheet as I type. I use this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$7" And Target.Value = 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = True
Else
If Target.Address = "$C$7" And Target.Value <> 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = False
End If
End If
End Sub
Recently I had to change the second sheet that instead of manually entered values, formula results appear in cells. And the above code doesn't work anymore, columns are always stay unhidden. How can I achieve what I want? I need columns get hidden if formula results =1.
View 4 Replies
View Related
Apr 11, 2009
I 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 Related
Sep 28, 2011
On my 'Report' worksheet in cells I1 to Z1 the formula returns "" if there's no data in that month or "1" to "18" depending on which month it is. e.g. if it's a 12 month accounting period then 6 of the columns will have no data in them and will have "" in row 1, the other columns will return 1 - 12 in row 1; if it's a 15 month accounting period then 3 of the columns will have no data in them and "" in row 1 and the other columns will return 1 - 15.
The VBA code below is part of a longer macro on my 'Data' worksheet. The rest of the code works fine and I just wanted to add this bit to hide the blank columns on the 'Report' page.
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculate
With Sheets("Report")
Dim i As Integer
If Intersect(Target, Range("I1:Z1")) Is Nothing Then
Else
For i = 9 To 26
[Code] ........
View 4 Replies
View Related
Feb 24, 2012
macro below that will only hide columns if all the rows (e.g. row 8 to 18) have no value?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("U18:AB18")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
[Code].....
View 8 Replies
View Related
Aug 30, 2008
I have a worksheet that lists the months of the year on row 3 from columns C through N.
What I am trying to do is to be able to select the month I want from a drop down list or combobox and then hide the remaining columns based on the selection. For example if JAN is selected the months FEB through to DEC would be hidden.
View 9 Replies
View Related
Aug 23, 2013
I have a very long spreadsheet and want to keep it manageable by hiding rows until they are needed.
Example:
Column
Row A B C D E
1 Line1
2 Reg
3 OT
4 Min
5 Line2
6 Reg
7 OT
8 Min
I want to click on A1 and auto hide rows 2-4. Then I want to click on A1 and auto unhide rows 2-4. Then I would copy the idea to Line2, etc.
View 4 Replies
View Related
Mar 18, 2014
I want to click on a cell and automatically hide the 3 rows underneath where I clicked. Then unhide them when clicked again.
I will assign the vba code to the specific cells of the sheet where I need it. But it will always hide/unhide the 3 rows underneath the click.
View 2 Replies
View Related
Aug 4, 2013
I am trying to automatically hide columns based on dates. I have a set of dates ranging from the beginning of August this year until the end of December 2019. I am trying to hide all columns that are at least a day ahead of the current date. Today on 8/4/13 it would show all columns up until today along with tomorrow 8/5/13.
8/1/2013
8/2/2013
8/3/2013
8/4/2013
8/5/2013
View 2 Replies
View Related
Jul 31, 2014
with a simple code that could:
If column K2:k466 is blank or says "No" then columns L & M are hidden. If it says "Yes" it is not hidden.
Also,
If Column N2:N466 is blank or says "No" then Columns O,P,Q,R are hidden. If it says "Yes" they are not hidden.
Finally,
If Column V2:V466 has a zip code (or number) then columns T,U,W,X,Y,Z,AA,AB,AC are present. If there is no value in any of V2:V466 then they are hidden. .
View 12 Replies
View Related
Mar 28, 2009
Is there a way of a user can add a number in a cell and this would hide a different amount of columns.
OR :
Ideally I would prefer a drop down with dates and the user could select a forward date and all the columns with dates up to that would appear. This would mean they could look as far our as required and all unwanted data would be hidden.
View 4 Replies
View Related
Jan 29, 2013
a code that will search each cell across 4 columns and hide the row only if all cells are blank. The macro should search columns "b", "c", "e", and "f" to display all rows where at least one of the cells has a value.
Ex.
Col.B Col.C Col.D Col.E Col.F
1. 123 xxxxx 150
2. 56 xxxxx 50
3. (blank) (blank) xxxxx (blank) (blank)
In this ex. row 3 would be hidden.
View 1 Replies
View Related
Jun 9, 2013
Sub Button294_Click()
If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub
View 4 Replies
View Related
