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.
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.
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.
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.
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
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.
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.
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.
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.
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
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.
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.
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.
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"?
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.
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.
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.
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,
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
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.
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.
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'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.
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.
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
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?
