Hide Rows Based On A Criteria

Oct 6, 2009

What am I doing wrong here?

I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.

ADVERTISEMENT

Excel Macro / VBA That Will Hide Rows Based On Multiple Criteria

May 23, 2012

I would like to hide rows for data that does not meet specific criteria. For example: If a user selects room number 101 from a drop down in B1, I want to filter data in range A3:F1000 to show me only room 101 rows (A column) where C column contains a value larger than zero OR D column contains a value larger than zero OR E column contains a value larger than zero. I do not want it to return rows where 101 may be in other columns beside A.

Sample:

A B C D E F
__________________________________
1 ROOM: 101
2
3
4 101 XX 1.2 0 0 P
16 101 YA 0 0 1.1 L
23 101 JJ 3.2 2.1 0 L
55 101 JJ 0 0 1 P

So, if a row contains 101 in column A and all three values in columns C, D, and E equal 0, then those rows will be hidden.

View 8 Replies View Related

Hide Rows Meeting Criteria

Sep 3, 2007

Searching through hide/unhide topics seem to yield more complicated codes then my meager needs. Within my worksheet I have a column BD which contains a list of values,

ie:

BD
1
2
3
6
3
2
1 etc..

I've created a toggle button "HideRow", which I would like to use to hide/unide only the rows containing value "2" within column BD.

View 2 Replies View Related

Hide Rows On All Worksheets Meeting Criteria

Aug 7, 2007

I have the following code that works well on a single worksheet

Sub hide()
For Each rw In ActiveSheet.UsedRange.Rows
If rw. Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then rw.Hidden = True
Next rw
End Sub

However I want it to run on each worksheet in the workbook when I run the macro. I tried this but it seems to loop continuously on the first worksheet. When I press escape the line "next row" is highlighted in yellow by the debug in the VBA Editor.

Sub hide()
Dim ws As Worksheet
Dim rw
For Each ws In ThisWorkbook.Worksheets

For Each rw In ActiveSheet.UsedRange.Rows

If rw.Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then rw.Hidden = True
Next rw

'Next ws
End Sub

View 6 Replies View Related

Hide Rows Meeting Date Criteria

Apr 4, 2008

I have a macro that hides rows in my workbook if certain criterias are met. I'd like to add a criteria to the logic that only hides rows if the date in cell 17 is less than 3 months from today's date. The date in cell 17 is shown in this format: 25-03-2008.

Sub ForceHide()
Dim ws As Worksheet
Dim line As Integer
Dim Endline As Long
For Each ws In Worksheets
If ws.Name <> " Total" And ws.Name <> "Batch" And ws.Name <> "Summary" And ws.Name <> "PivotTable" And ws.Name <> "Fields" And ws.Name <> "RTP" Then
With ws........................

View 3 Replies View Related

Hide Sheets Based On Criteria

Sep 3, 2006

i would like to hide different sheets at one shut. i tried this, but did not work:

Dim sh As sheet
For Each sh(1 3 5 7 9).Visible =xlSheetVeryHidden

View 6 Replies View Related

Hide Workbook Tabs Based On Certain Criteria

Jun 10, 2014

I have tabs within my workbook that are pulling from other tabs in the same workbook. There are 30-40 tabs, and only 10-15 are used at a time. Is there a way to hide tabs that are not being used? If I do a calculation in A1 and it's greater than 0, I'd like the tab that is pulling from A1 to be visible. If A1 is 0, then I'd like to hide it.

View 14 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 To Hide / Show Columns Based On Filter And Multiple Criteria

Mar 8, 2014

I have a very large table and i need to be able to Hide/show specific ranges based on:

Filter +and+ specific cell values in columns

brief example of the table : tablee.png

So...
1. Filter Column "B" (in this case we select "HELPING")
2. Auto hide/show collumns. - IF "C1" = "Required" THAN Show "C:E", IF "C1" ="N/A" , HIDE "C:E" and so on for every column like above.

There are over 80 columns like the "C:E" range. and I only need to show those that are "Required".

View 1 Replies View Related

Hide/Show Pivot Table Data Based On Criteria

Dec 12, 2007

I want to write a VBA code, so I can apply dates criterias to my pivot table. Say, I have 1-Dec-2007 in "C2" and 10-Dec-2007 in "C3". Now I want my pivot table to show me the dates between those two dates and the data that goes along with it. I have written this code, but it keep debugging:


Sub FilterDates()

Application. ScreenUpdating = False

Sheets("PnL").Select

Sheets("PnL").PivotTables("PivotTable3").PivotFields("Date") _
.PivotItems("01/01/1950").Visible = True 'to always have 1 populated

View 9 Replies View Related

Hide/Show Pivot Table Data Based On Date Criteria

Feb 22, 2008

I have looked at the below Hide/Show Pivot Table Field Items help web pages:

Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria

I am trying to use the above, but with dates in the following format in each cell:

YYYYMM
200612
200701
200702
200703
200704
200705
200706
etc, etc

The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the ďNonValidCriteriaĒ prompt.

Sub HideByCriteriaYYYYMM()
'Declare variables
'SEE: [url]
'SEE: [url]
Dim pt As PivotTable, pi As PivotItem
Dim lMonth As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation

View 4 Replies View Related

Hide Rows Based On Two Conditions?

Apr 3, 2013

I'm trying to hide rows in an excel sheet based on two stipulations. I want to hide the row if it finds a particular value in column B and a different particular value in column K, otherwise i want it to do continue looping until it has hidden all rows that meet both stipulations.

VB:
Sub hide_loop()
Dim bl As Excel.Range
Dim blrange As Excel.Range

[Code]....

Currently I have tried different approaches, like a Do Loop, but I could not make that work, and this seems closer. The problem I'm having right now is that with this loop it hides everything found in column B regardless of what is in column K. I suspect this is because of the code following the if statement.''

View 9 Replies View Related

Hide Rows Based On Autofilter

May 7, 2014

I have a spreadsheet that is split in two parts , one with headers in row 16 with data flowing down to row 190. In row 192 I have another set of headers with data flowing down from that to row 300.

I have a userform (roughly at cell B13 in the attached) that filters the first block of data into either Company, Syndicate, EU Corporate or ALL. (ignore the other filters) which feature in range B18:B190

What I want it to do, is that when one of the three options is selected, ie Company, is that all rows from 193 down are hidden other than those that are also Company (in the test case there is just one row). The same is true for when Syndicate or EU Corporate are selected in the userform, and if the ALL is selected then none are hidden.

View 2 Replies View Related

Hide Rows Based On Date

Jan 2, 2009

I need to write a macro that will hide several rows if a calculated date's month is not within the month of the report.

Example:
If month of date in cell B20 is not the same as the month of date in cell A5
then hide rows 20 thru 30 if it is the same month, display rows 20 thru 30.

I need to do this comparison twice. If month of date in cell B40 is not the same as the month of date in cell A5 then hide rows 40 thru 50 if it is the same month, display rows 40 thru 50. The dates in B20 and B40 are the result of a calculations (Date in cell A5 plus some number of days) if that makes any difference. I don't think it should.

View 3 Replies View Related

How To Hide Rows Based On The Value Of Two Columns

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

Macro To Hide Rows Based On 'Sum'

Dec 26, 2009

I want a code to Hide the row if "sum of numbers in columns three thru last column" = 0. Following is a code I use to delete a row if that is blank. The difference here is that I dont want to to check first two columns and I want to hide them rather than deleting.

View 4 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 On Combobox?

Apr 12, 2012

I am creating a survey and have used combox,i want hide rows if the result in the combobox is no

View 1 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 Value In 1 Column

Dec 30, 2009

I wrote some simple code to work through a number of rows and hide the rows which contain a value of "XXX". For some reason, when the code hits the line denoted with? it hides the first row of "XXX"'s but then starts all over again at very first line of code outside the loop.

Sheet1. Range("start_1").Select 'cell c53

Do Until ActiveCell.Value = "EOF" 'cell c266
If ActiveCell.Value = "XXX" Then
Selection.EntireRow.Hidden = True '????
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

View 2 Replies View Related

Hide Rows Based On Color

Apr 4, 2008

I have the following code to hide rows based upon whether or not the cell in column A is black or not.

Sub hide_black()

Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("A2:A36635")
For Each MyCell In Rng
If MyCell.Interior.ColorIndex = 1 Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub

I was thinking of taking this one step further and having the rows hide automatically once the cell is turned black allowing the user to bypass having to click on a macro button.

View 6 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

Hide Rows Based On Dropdown Selection?

Jan 8, 2005

I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.

View 9 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

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

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

Make Rows Hide In One Worksheet Based On Value In Another?

Apr 30, 2012

I have a worksheet "Feature Segments" that has a value in cell B40 and C40, if the value in these is "Off (Default)" then I need rows 22 and 23 on Worksheet "Summary" to hide.

I have put this code on the features segment tab but it is not working.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Address = "B40" Then

[Code].....

View 3 Replies View Related

Hide Rows Based Over Multiple Columns

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

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

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

Hide Rows Based On Blank Cells?

Feb 21, 2014

I am attempting to hide a series of rows based on if the cells in that row are blank. The catch is that the field of data in the column may vary as follows:

D E F G
x x X x
x X x
x

I would like to eliminate all the rows past the last X value in Column D for example

Below is the code I am attempting to use

Rows("41:60").Select
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 42

[Code].....

View 8 Replies View Related

Hide Rows Based On Drop-Down Selection

Jan 8, 2005

I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.

View 9 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 Rows Based On Combobox Selection

Dec 16, 2006

I know there are a lot of hide row threads... i've searched for ages... but (unfortunately) i can't a solution that addresses my problem and I'm stuck. Please help!

I have a data between rows 10 and 1310 and I would like to use a combo box option to select and view certain rows of data only. I don't want to use auto filter because i have a separate macro using an auto filter on the same data - they won't play together.

So in short - combo box will contain values from column A. When value is selected from combo box hide all non matching rows based on column A values.

So i have added a combo box to sheet1 and populated it with the following workbook code...

Sub Workbook_Open()
With Sheets("Sheet1").ComboBox1
.AddItem "Select Product Type"
.AddItem "Apples"
.AddItem "Mangoes"
.AddItem "Oranges"
.AddItem "Clothes pegs"........

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

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

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 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 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

ADVERTISEMENT