Hide & Unhide Rows Based On Condition
Aug 17, 2007
Attached is a portion of the file I will be working with, just to illustrate what I would like to do.
I have a macro that copies and pastes data - it uses these Tasks as templates. The macro copies these rows for each task that is required under the headings (Task #1 - Task#?). What I would like is to hide the rows under Station Maintenance Task #0 and it's 6 rows that are associated with it, as well as Task #0 under CMS and the 12 rows associated with it. In order for the macro I already have work properly is that these would be unhidden for the macro to run and then re-hidden afterward.
I searched on the forum for this topic, and found hide/unhide where you would put in the beginning row and ending row - however since there will be numberous rows added in, this type of defining rows would not work.
View 4 Replies
ADVERTISEMENT
Aug 22, 2008
In the included sheet I have 5 groups of data with five categories in column A. Current, Plan, Plan Var, Prior, Prior Var. I have included the button “Show Options” that opens the userform I created and gives 5 options. What I want is the user to be able to select any number of these options and then upon “ok” the rows in the sheet that weren’t selected are automatically hidden. If the procedure is completed again and a differen set of options is selected I want it to unhide any hidden ones that were selected and hide any that werent selected.
So if just current is selected the sheet will show 5 rows of current and nothing else. If current and prior are selected it will show current prior current prior current prior... etc.
I have some hide code that I created in the file as well.
Sub NotCurrentHide() ...
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
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
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
Feb 1, 2013
I have a worksheet that allows you to choose 1-20... depending on the selection it should unhide specific rows but I keep getting errors...
Code that will allow you to unhide other worksheets in the same book????
View 1 Replies
View Related
Jun 17, 2014
I have the following code which works perfectly. It needs to hide rows 3:60 based on the value in K2.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K2")) Is Nothing Then
If Not IsNumeric(Range("K2").Value) Or Range("K2").Value = "" Then
Range("A3:A60").EntireRow.Hidden = True
Else
Range("A3:A60").EntireRow.Hidden = False
End If
End If
End Sub
The problem I am experiencing is that I want to use a formula in K2. K2 must get its value from a cell on a different sheet.
If I manually delete the value in K2 the rows hide and if I manually enter a 1 in K2 the rows unhide, however I want to enter a formula in K2 that will result in either a 1 or blank cell.
View 3 Replies
View Related
Jul 12, 2013
I have a calculated cell (D13) which can either be (All), Grade or Rate.
If D13 = (All), I would like Rows 19:52 to be hidden
If D13 = Grade, Only Rows 19:20 to be hidden
If D13 = Rate, Only Rows 22:52 to be hidden
View 2 Replies
View Related
Jan 2, 2014
I am trying to create a macro that will work on my master summary sheet. The sheet pulls information from other worksheets. I would like to have a macro that will look at range E7:E356. If the value is 0 then hide, but if the value changes then unhide the row. I have tried autofilter but it will not update automatically.
View 1 Replies
View Related
Feb 4, 2008
I need to do: Based on whether a cell returns "Export" from a vlookup function, I need a macro to run, hiding some cells and unhiding others at the same time. Also, if possible, if the cell's value changes from "Export" to something else, I need it to revert back to the original hidden/visible rows.
View 9 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
Mar 19, 2014
I am building a sheet to display deadlines (rows) by project (columns). I would like to have it automatically hide the rows and columns based on the date of the deadline. If the deadline is today or 1 week from today the associated rows and columns should be visible, otherwise, I would like to hide them.
My table is A1:N9, with A1 being a blank, row names A2:A9, and column names B1:N1.
View 4 Replies
View Related
Mar 7, 2008
I want by using some code I've seen on this forum or using the macro writer and then tweaking the code. So with that said, I've written the attached code but I know there is probably an easier way to write it. It cycles through about 12 sheets using the same below code, but I didn't list that code.
Sub Hide_Rows()
Dim i As Integer
For i = 3 To 418
Sheets("AFA - UMBI").Select
If ActiveSheet. Range("b" & i).Value = "2008-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-1" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
End If
Next i
End Sub
View 7 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
Apr 3, 2014
I highlighted a selection of rows and clicked HIDE, now I want to unhide certain rows and when I highlight the rows above and below and right click - unhide nothing happens. I need to Unhide to find something.
View 2 Replies
View Related
Apr 3, 2014
I have a button that I want to hide/unhide a section of rows.
This is the code a have so far:
[Code] .....
So I've named a a section of rows "rngHideRows". I've tried to use the command .Range("rngHideRows").EntireRow.Hidden = True (and viariants of this) but nothing I try works.
View 7 Replies
View Related
Jun 18, 2014
code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Sheet2")
Select Case Target.Address(False, False)
Case "B12"[code].....
I need to change it so that it hides a range of rows, say (6:26) instead of adding a line of code for each of the rows from 6 to 26 that I want to hide
Also how can I go about hiding the same rows on 5 more sheets, can I add more sheetnames after "Sheet2"?
View 3 Replies
View Related
Jun 19, 2007
Need a macro which will hide rows having 0 values. On running once it will hide those rows having 0 values and on pressing second time it will unhide those hidden rows and so on.
View 14 Replies
View Related
Mar 7, 2009
I need to hide / unhide some rows in a sheet with the help of a activex checkbox... I want remove, let's say row 20:24 and row 34 with one checkbox.
View 6 Replies
View Related
Apr 2, 2009
I have a code that will hide all rows that have a "-" in Colum A, leaving me with the useful data.
However, this data is pulled from a front sheet that has data added to it.
What I would like my code to do is to relook at all the hidden cells, and if their value has changed from "-" to what ever the data may be then to unhide that row, displaying the data.
The code I'm using currently is: ....
View 10 Replies
View Related
Jan 11, 2010
I have a list box on a worksheet that contains the following names: field, vessel, pipe, structural. I would like to be able to unhide certain rows based
on the name shown in the list box. For example, if I choose field it would
unhide rows 3 thru 12, if i choose vessel it would unhide rows 13 thru 20, so on so forth. If nothing is choosen in the list box then rows stays hidden.
View 9 Replies
View Related
Jan 20, 2010
i'm newbie here. i need to hide & unhide rows using checkbox in excel but i stuck. i write these simple code and it doesn't work at all. my bad..
View 2 Replies
View Related
Oct 26, 2011
I have a workbook with multiple tabs. Each tab has about 600 rows. In each tab, I need to hide/unhide rows based on the value of a particular column in that row. For example,
Row1: 10,7.89,John,TRUE
Row2: 16,9.08,Ram,FALSE
Row3: 98,8.09,Joseph,FALSE
Row4: 76,1.23,Harry,TRUE
Using the 4th column (that has either TRUE or FALSE), I need to hide entire row. I am using a loop on the range and hiding each row. But it is taking about 4 minutes for each tab to loop through the 600 rows and hide/unhide the required rows. Is there a faster way to achieve this? Following is my code.
Sub Toggle_Rows()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Range("D1:D600")
If UCase(Cell.Value) = "TRUE" Then
Cell.EntireRow.Hidden = Not Cell.EntireRow.Hidden
End If
Next
Application.ScreenUpdating = True
End Sub
View 1 Replies
View Related
Sep 21, 2012
I think this is fairly straight forward but my limited VBA skills have me going round in circles. I would like to have a button that hides and unhides rows where Column L has blank values but only from row 80 to 200 only. I would also like the button text to change from "Hide rows" to "Unhide Rows".
From Row 80 to 100 hide rows where there is no value in column LButton should be clickable to hide rows and then clickable to unhidebutton text changes.
View 2 Replies
View Related
Nov 20, 2013
I am struggling to come up with a vba code that allows me to search column B, Rows 21:89 for blanks then hide/unhide the associated row. I would like it to be one macro so that I don't have to have two buttons on the sheet to hide/unhide.
View 5 Replies
View Related
Jun 13, 2007
I have a macro that works fine with just numbers as a test run when I use it in the desired workbook/worksheets (where the values are returned via Indedx/match functions) there appears to be a small problem. (Below is the relevant post.)
[url]
Background is that I load raw data into a monthly account sheet which has the appropriate cost centre codes for allocating expenses.
I then use a vlookup in combination with indirect to place the summary data by costcentre code into annual worksheet.
I then have a summary sheet for printing purposes so I can print out a single month expenses by code to staple to the appropiate monthly costcentre statement. This is done by using Index & match functions looking at the annual summary sheet
What seems to be happening is that the code is sometimes recognizing formulas as a value & therefore showing zero values when I want only values 0 (as I sometimes have minus values).
Also sometimes when I go to unhide the all the rows it also is recognising some formulas as having value.
I am using Xl 2003.
View 9 Replies
View Related
Dec 15, 2008
I'm using this VBA code which is hiding cells in rows 59:111. Some of these cells have formulas and some don't.
Private Sub Hide_Unhide()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 59 To 111
Rows(i).Hidden = (Cells(i, "b").Value = 0) + (Cells(i, "b").Value = "")
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Here is the problem I'm running into: I want this to run in the background so the user won't have to click anything to hide/unhide the cells whenever the options in the drop down list are changed and the data expands/collapses.
View 9 Replies
View Related
Sep 14, 2009
I am using several toggle buttons to hide/show rows of data related to the specific toggle buttons. I also want to hide and show the toggle buttons as well. When I try to do that the data underneath the toggle buttons is hidden but the toggle buttons don't hide with the cells.
A solution for the problem that I am having would be to have the ability to click on a cell as if it were a toggle button and hide/show rows of data when clicked.
OR
If there is a way to hide/show buttons as well would work too.
View 9 Replies
View Related
Nov 11, 2009
I've had luck with hiding rows with a checkbox, but I cannot get them to unhide when I uncheck the box.
Here's my scenario - (using Excel 2000) In an estimating spreadsheet I have a print range of A1:N74. What I'd like to do is add a checkbox or button so I can hide and unhide rows based on a null or zero in column range b5:b62. This is for printing purposes, but I can't get a 'beforeprint' event to work either. All I need is box checked and cells hide, box unchecked and cells show (unhide).
Sub CheckBox1_Click()
Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("B5:B62")
For Each MyCell In Rng
If MyCell.Value = "" Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub
View 9 Replies
View Related
Feb 26, 2010
I have a workbook that has 500 rows. In order to be able to print the spreadsheet, I have added a Macro in that hides any rows that have a "0" in the A column. I then put an if/then formula (ex. if(isblank(A5),0) so that if the cell was blank and 0 would be there and therefore the row would be hidden.
The problem now is that I want a new row to become unhidden everytime the row above has data in it. So, if row 5 gets data put into it, row 6 would become unhidden. The problem I'm running into is that the data in column A is peoples names, and therefore each row will have a different name (i.e. different data).
1. Is there a macro to do this?
2. (This may be a really stupid question, but...) Can you run 2 macros in the same sheet?
View 9 Replies
View Related
