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.
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.
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.
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
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........................
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.
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.
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:
Application. ScreenUpdating = False
Sheets("PnL").PivotTables("PivotTable3").PivotFields("Date") _ .PivotItems("01/01/1950").Visible = True 'to always have 1 populated
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
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
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.''
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.
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.
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 ......
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.
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
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.
Private Sub Worksheet_Change(ByVal Target As Range) If Range("E6") = 0 Then Rows("1:6").Select Selection.EntireRow.Hidden = True
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.
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")
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.
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.