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

Automatically Hide / Show Rows Based On Cell Value

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

Automatically Hide / Show Rows Based On Cell Value

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

Hide Rows Based On Two Cell Values

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

Hide Rows Based On Other Cell Values

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

Hide Range Of Rows Based On Cell Values

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

How To Automatically Hide Rows Based On Value In Column Q

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

How To Automatically Hide Rows - Based On Time

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

Automatically Hide Rows Based On A Value In Column Q

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

Automatically Hide & Show Rows Based On COUNTIF Result

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

Macro Code Automatically To Hide Rows Based Condition

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

Hide Blank Rows Based On Values In Other Rows

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

VBA Hide Rows Based On 4 Different Values With Combobox

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

How To Hide Specific Rows Based On Values In A Different Worksheet

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

Automatically Hide/Unhide Columns Based On Cell Text

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

How To Update Rows Based On New Values So That Those Are Pulled Automatically

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

Hide Rows Based On Value In Cell G2

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

VBA Hide Rows Based On Cell Value

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

Hide Rows Based Upon Value In Cell?

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

Hide Rows Based On Cell Value Inputted From Another Tab

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

How To Hide/unhide Rows Based On Cell Value

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

Macro To Hide Rows Based On Cell Value

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

Auto Hide Rows Based On Cell Value In Same Row

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

VBA To Hide Rows Based On Cell Content

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

Hide/Unhide Rows Based On 1 Cell

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

Hide Rows Based On Cell Value Being Empty

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

Hide Rows Based On A Cell Reference

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

Hide & Unhide Rows Based On Cell Value

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

Hide Or Filter Rows Based On Cell Value

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

Auto Hide Rows Based On Cell Above

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







Copyrights 2005-15 www.BigResource.com, All rights reserved