Hide Rows Based On Values In A Cell Automatically
Apr 8, 2014
I am trying to hide rows based on values in a cell in that row. I have formulas in column xea that yields a 0 or 1 and I want to hide all rows with 0.
The code below works but I have to run the macro. I want it to happen automatically when the cell values change and I have not been able to figure it out.
Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each c In Range("xea1:xea350")
[Code] ........
View 9 Replies
ADVERTISEMENT
May 7, 2013
Trying to have A1 in Sheet1 having three values 1,2,3 if A1 = 1 hide rows from 1:5 and 10:1500 in three Sheets2,3,4
View 8 Replies
View Related
Feb 27, 2008
I wrote a small code to hide some columns if a certain cell is equal to a certain string.
The cell is actually a drop down list and when they select a certain one, I want it to hide 2 columns. So I wrote the code with sub name Action, but I want it to be running all the time. I tried to achieve this by writing the following code however it gave me error 438 for my 2nd line.
Sub Auto_Open()
Range("A1").OnEntry = "Action"
End Sub
Auto Merged Post Until 24 Hrs Passes;Oh, by the way error 438 states: Object doesn't support this property or method
View 3 Replies
View Related
Feb 1, 2009
Is there any way to hide a row if columns 6 and 7 both show either a "-" or a number lower than 0.5? Cells in both rows contain a VLOOKUP formula.
View 9 Replies
View Related
Sep 8, 2007
I need to hide rows on a worksheet based on entries from DATA VALIDATION dropdown lists on another. Due to the fact that all the entries are on a different page, and there is no active cells or entries made on the "second" worksheet, the WORKSHEET_CHANGE events will NOT work. Trust me, I have spent two days searching the net for a possible answer and trying every trick I could find. Not to seem redundant or a pain, but right from the git-go, FORGET the WORKSHEET_CHANGE event as an option. I need a way to fire the macros from a formula change in a linked cell, and it won't be an active cell, nor will there be any kind of entry made on that page at all. ALl the entries (choices) will be made on another sheet, but the result needs to be hiding the rows not required based on the choices I make. There are over 300 lines and when I am done there will be only approximately 35-40 left. Right now the people using this are actually making copies of the workbook, and then deleting the worksheets they don't need (I am hiding those with an array) and then deleting the lines they don't need on the worksheet remaining. (Stupid and slow I know, but I didn't develop this workbook, I'm just trying to bring it out of the stone age) Here is some sample code with what I want to do, except it is of a WORKSHEET_CHANGE nature and will not work.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("INPUT!M6"), Target) Is Nothing Then
Rows("11:50").Hidden = True
If LCase(Range("INPUT!M6").Value) = "100" Then Rows("31:50").Hidden = True
If LCase(Range("INPUT!M6").Value) = "100" Then Rows("11:30").Hidden = False
If LCase(Range("INPUT!M6").Value) = "225" Then Rows("11:30").Hidden = True
If LCase(Range("INPUT!M6").Value) = "225" Then Rows("31:50").Hidden = False
If LCase(Range("INPUT!M6").Value) = "0" Then Rows("11:50").Hidden = False
End If
End Sub
View 9 Replies
View Related
Aug 13, 2014
I've been trying to put together some code to hide a range of rows on one page based on cell values on a different page. So basically Sheet1 cell A1 value is 10, A2 value is 20. When macro is run via button rows on Sheet2 from 10-20 would be hidden. The issue is the values in the cells on Sheet1 will vary. I thought something like the following would work but it doesn't.
[Code] ....
View 6 Replies
View Related
Jul 11, 2007
I am trying to implement the following:
After the user selects something using the validation list in cell F38, the macro should check in column Q in the rows 44 till 425 for "hide". For each row in which it finds "hide" the entire row should be hidden.
I have the code below but it does't work yet. It calculates for ages and afterwards only the first 2 rows of the 381 are hidden (probably because the third row is not "hide").
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim ThisCell As Range
Target = Range("F38")
[code].....
View 9 Replies
View Related
Apr 11, 2013
I have a set of 3,000 data points. The data is in one second intervals, however I am interested in seeing what the data looks like in a graph if I only had a point every minute. I have the following set up.
A B C
TIME X Y
What I want to do is to leave the first cell unhidden, and then hide the next 59. And then repeat this so I narrow the data down to one second each. I'm looking to do this multiple times, so I can create a graph each time in order to get an idea of what the data would look like if I only had a point for every minute.
Is there a macro that I could write, that would hide every cell except every 60th one? I was thinking that I could hide it based on the time. What would the code look like if my time was in 00:00:00 format? It's increasing, so I need it to hide cells that aren't equal to XX:XX:01, for example.
View 3 Replies
View Related
Jul 11, 2007
After the user selects something using the validation list in cell F38, the macro should check in column Q in the rows 44 till 425 for "hide". For each row in which it finds "hide" the entire row should be hidden.
I have the code below but it does't work yet. It calculates for ages and afterwards only the first 2 rows of the 381 are hidden (probably because the third row is not "hide").
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim ThisCell As Range
Target = Range("F38")
Set MyRange = Range("Q44:Q" & Range("Q425").End(xlUp).Row)
For Each ThisCell In MyRange
If ThisCell.Value = "hide" Then
ThisCell.EntireRow.Hidden = True
End If
Next ThisCell
End Sub
View 9 Replies
View Related
Oct 26, 2008
What i would like to do is Hide Rows Based on Cell Value in Multiple Sheets & Multiple Columns
and i need the macro to be fast
Sheet1
If the value in Range BE11:BE160 equals 1 don’t hide the row
If the value is “0” or “ “ then hide the row
Sheet2
If the value in Range BE11:BE160 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet3
If the value in Range BE11:BE160 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet4
If the value in Range O1:O150 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet4
If the value in Range B1:B150 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
View 3 Replies
View Related
Apr 26, 2008
I have a spreadsheet that calculates percentages and then outputs the results to a pie chart. There are 9 different percentages being graphed in cells A41 to A49. The chart looks weird if any of the percentages end up being 0, so I have the formula set to add 0.00001 to each calculation (so they show up as 0% and display on the chart as 0%, but truly are 0.00001). I would like it so that if any of these 9 percentages ends up being 0 (or really 0.00001) that the row automatically hides and thus won't display on the pie chart. How can I create a macro that automatically runs to accomplish this, and automatically updates as percentages are recalculated.
View 3 Replies
View Related
Nov 3, 2006
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
View 2 Replies
View Related
Nov 16, 2011
I have a vba to hide certain rows based on 4 values (either 15, 20, 25, 30) are the choices in the 'ComboBox' drop down list. I also have the rows grouped so i need to make sure to collapse those back each time. Here is the vba i'm using, but its not quite right for some reason.
PHP Code:
Private Sub ComboBox1_Change()       Â
If Range("J8").Value = "15" Then           Â
Range("A185:A244").EntireRow.Hidden = True           Â
Range("A245:A304").EntireRow.Hidden = True           Â
Range("A305:A364").EntireRow.Hidden = True           Â
Range("A365").EntireRow.Hidden = False           Â
[Code]...
View 1 Replies
View Related
Mar 7, 2014
Say I have two worksheets, "Sheet1" and "Sheet2". Let's also say A1 in Sheet1 could have one of four values: cat, dog, rabbit, mouse. Now, based on which value cell A1 on Sheet1 is, I need different rows hidden in Sheet 2. For argument sake let's say if cat is chosen rows 1-5 are hidden, if dog, 6-10 and so on.
View 5 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
May 27, 2014
I create a report that shows one set of numbers against another set of numbers based on a transactions list I pull monthly. The Report tab is really simple and is based on the sumifs. Sometime, new accounts are created that I don't know about. They show up in the transactions table but not on the Report tab. Is there a way to automatically add new accounts/rows on my summary sheet so that no account is missed? Is there also a way where columns A and B on the Report tab are unique values that are pulled from the transactions tab? Hence, if a new number and description are added, the list just picks it up. In my case however, there are tens or hundred of lines with the same accounts/descriptions.
View 3 Replies
View Related
Nov 18, 2011
I am using windows 7, and excel 2007. I want to hide rows bases on a value in cell G2 which is a date field
Example, if G2 is greater than 11/16/2011 then hide rows 1969 through 2032.
View 6 Replies
View Related
Mar 27, 2012
With a piece of code that will hide an entire row if a cell value in that row does not equal the cell value of another row.
Example
Cell d3 = Feb 2012.
Range C5:c252, = a mix of Feb 2012 and Mar 2012.
I want to hide all the Mar 2012.
However if Cell d3 = Mar 2012 then the range C5:C252 will also = Mar2012
I want to see all the rows within that range.
View 4 Replies
View Related
Apr 9, 2014
I am trying to auto hide rows in a spread sheet. the start row will always be 55 and the end row will be 55 + the value of cell A38 in sheet titled "Main".
View 2 Replies
View Related
Dec 13, 2013
In this example on Sheet 2, I'm trying to hide rows 1-6 if 'E6' is 0, and hide rows 15-26 if 'E18' is 0. These cell values are equal to the same cell in Sheet 1.
I've tried different codes I've found online, but I'm not very experienced with VBA so I can't get it to work. I can get rows 1-6 to hide, but it doesn't do it automatically whenever a zero is inputed into Sheet 1. It only works if I enter something into another cell on Sheet 2.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E6") = 0 Then
Rows("1:6").Select
Selection.EntireRow.Hidden = True
[Code]...
View 5 Replies
View Related
Jun 17, 2009
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.
View 14 Replies
View Related
Jun 17, 2003
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
View 9 Replies
View Related
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
Jun 23, 2006
If I have data in range A1:Z99 I want to write a macro to hide all the rows that have a zero in the A column.
View 9 Replies
View Related
Jul 3, 2007
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.
View 9 Replies
View Related
Dec 15, 2008
I have read other posts about this but I am still a little confused as most macros that have been made are tweaked to suite the users individual needs.
What I want is something like this to work in the active sheet:
If cells D2:D55 = ""
Then Hide.EntireRow
If cells D2:D55 = "has any value"
Then Show.EntireRow
The values in D2:D55 are populated by a VLOOKUP depending on what someone chooses in a drop down validation list, however not all the rows are always required so I would like to hide them to save some space on my form.
View 9 Replies
View Related
Apr 22, 2009
How can I automatically hide rows based on a cell reference? For example, If A1=0 then hide row 1.
View 9 Replies
View Related
Feb 26, 2010
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.
View 9 Replies
View Related
Dec 21, 2008
I need to hide rows based on a cell value. In cell B4 I list how many students are in period one.
I have enough rows for 35 students found in A10:A44. In C10: C44 I listed numbers 1-35.
So I need any row that has a number in the C column higher than what was entered in B4 to be hidden, but when cell B4 changes I need rows to unhide if their row is less than new value.
So here was my attempt: ...
View 9 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
