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: ...
i have been able to get most of my VB work on my own (i'm learning). Currently I'm having a bit of difficulty in hiding a row based on a cell value, potentially due to cells being merged.
I'd like to select from a dropdown cell to filter on a row's value. Attached you will find a sample sheet that works named "Working" in it's current state and the one "TEST" that does not filter every other line item.
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.
basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.
the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.
where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
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.
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.
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 RelatedIn 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]...
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.
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
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 RelatedIf 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 RelatedI'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 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.
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 RelatedHow can I automatically hide rows based on a cell reference? For example, If A1=0 then hide row 1.
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.
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
I'd like to hide rows based on the value of a specific cell (that specific cell will always be C14, and it will have up to 8 variables selectable from a drop down list).
For example, if C14 = Innovative/exploratory, i'd like to hide rows 15-45. If C14 = Technical Service, i'd like to hide rows 15-32, and 42-44. I'm not familiar with VB, but am a quick study -
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
Cell A1 can be left blank or have any value 1 to 99999 inserted by the user. Default value is null. My data starts on row 5 and currently goes to row 62, but will include more rows over time. I want to automatically hide rows 5 to 62 (and higher later on) where the value in Column C (C5, C6, C7, etc) is less than or equal to the value in A1 as long as A1 isn't null. If A1 is null, then no row hiding would be done. Ideally would be great to have msg also that says "No rows hidden" or "10 rows hidden", but not critical - just nice to have if you can provide such easily and quickly otherwise I'll just try to learn it later on.
View 7 Replies View RelatedI have two sheets say:
Sheet1
Sheet2
Sheet1 has a few comboboxes saying (YES / NO) conditions Which are assigned to particular cells (for Ex: say Combobox1 value assignes to Sheet1!B5 )
If Sheet!B5 = YES some rows in Sheet2 Say ( Row12 ,Row 15,Row 16) has to be hide.
I will add a command button to sheet1 and call macro if i click command button checking the conditions in sheet1 combo boxes..rows in sheets2 has to hide..
I would like to hide rows (A17:A180) based on the value in the cell. If the value in a specific cell is "X", hide the row...if anything else is in the column's cell (A17, etc), show it.
View 6 Replies View RelatedI have a parts index containing 500+ rows of data, I also have multiple columns in which I can sort the data.
I would like to have a button fixed in cell C4, D4, E4, etc... row 4 is the header, that when clicked, will filter all rows that have a selection (non-blanks) for only that column. So If I have data in various rows & column (C5:F500) that when I click on the button in cell D4, only the data listed in the various rows of D5:D500 are shown and columns C, E & F are hidden. Clicking cell D4 will show all data.
I can create the button so that it is sized specific to the cell and follows the cell sizing and I can create a button to filter only columns D or E or F, etc... but that means that each button is a separate VBA, macro.
A while ago I found a VBA code that allowed me to sort rows Asending/Desending depending on which button in which column was clicked... See inset: and this allowed me to add columns without changing the code. So I think If I can modify this code this will work great for me..
Sub SortTable()
'code written by Dave Peterson 2005-10-22
'2006-08-06 updated to accommodate hidden or filtered rows
Dim myTable As Range
Dim myColToSort As Long
Dim curWks As Worksheet
Dim mySortOrder As Long
Dim FirstRow As Long
Dim TopRow As Long
Dim LastRow As Long
Dim iCol As Integer
Dim strCol As String
Dim rng As Range
Dim rngF As Range
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 RelatedI wish to have a button on one sheet wich opens another sheet and hides all rows without "NO" in F4:F300
Starting code for button below:
VB:
Private Sub Kilaneset_Click()
Sheets("MSDS").Visible = True
Sheets("MSDS").Select
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'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] ....
I have a worksheet that contains 15 instances of a repeated table over 700 rows. Each table is 45 rows in size and is housed in between the natural page breaks in the spreadsheet.
These tables are populated from data form another worksheet but may not all be used (8 out of the 15 may be used but will always start from table 1 and there will be no missed tables).
In the very top right of the table is a cell value that is only displayed if the table is in use, so will be blank if not used.
Code that will hide multiple rows (45) based on a cell value being blank.
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
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