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
ADVERTISEMENT
Feb 27, 2012
(Excel 2010): Hide row if cell C in this row is empty.
I've just started using macros and I'm sure there is one for this problem.
View 5 Replies
View Related
Jul 2, 2014
My company has a canned template for some of the work we do and to avoid wasting too much paper I wanted to insert some extra code into an already programmed macro button (which sets the page breaks) to hide forms (both as individual worksheets and rows within separate worksheets) if the field that ought to auto-fill them is left empty.
View 3 Replies
View Related
Aug 1, 2007
I am trying to run a macro that will hide rows when one cell is empty and another is not. Example: hide row when cell g is empty, but cell b is not. Or something to that effect. So far I've only used this code, but I would like to know how I can modify the code to fit the parameters I need:
Sub HideRows()
On Error Resume Next
With Range("B1:B300")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction. Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub
View 3 Replies
View Related
Sep 3, 2006
I am getting values for my excel sheet from another department excel sheet . everything works fine. If there is no values in the rows in the Department sheet, then i need to hide the rows in my sheet. How to code this in VBA. When they add values to the rows then i should make the rows visble here. Kindy give me a sample of vba code to this or suggest me to solve.
View 9 Replies
View Related
May 13, 2008
I would like to rows based on multiple column conditions criteria. ie., if the columns N, O, P values are "", then hide the particular row. The logic given in the website here, i tried But, it is not 100% working. It works for a few rows at the start of the database & it works for the rows at the end of the database. In between, for a few rows, even if the column values are "" it does not hide those rows.
View 2 Replies
View Related
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
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
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
Jun 23, 2014
I am building a template ("Table") that will import data from 4 other spreadsheets and then format the data once its all in the template. I need to delete all rows where a name didn't import. The names are landing in column B (starting with B22), so I set up my code using an active cell loop macro to examine each cell to see if it was empty, and then to delete the row if it were. I've tried 4-5 iterations of code but nothing is working correctly.
Apparently when the fields are copied over from the other spreadsheets, some empty cells actually have something in them such that they are not completely blank. What syntax I can use so that I capture every instance of a blank/empty cell and delete that corresponding row? Some of the code I've tried is below.
[Code] .....
View 3 Replies
View Related
Mar 22, 2014
Im looking for a macro that hide empty rows. I found some simple macro but that are long to execute. While looking for a faster code, I found two codes that work pretty fast. But as I don't understand VBA I am not able to adjust them to my situation.
First macro: I am able to specify my range (B6:B77), but the macro applies to blank cells and I need to apply to "" cells.
[Code] .....
Second macro: very fast as well. Here, it applies to "" cells, but I am unable to specify a range. So the rows 1 to 4, which are empty, are hidden but should not.
[Code] .....
View 11 Replies
View Related
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
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
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
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
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
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 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
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
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
Dec 21, 2008
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: ...
View 9 Replies
View Related
Jan 30, 2009
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
View 9 Replies
View Related
Sep 29, 2006
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 -
View 4 Replies
View Related
Sep 8, 2007
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
View 9 Replies
View Related
Apr 8, 2008
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 Related
Jun 10, 2008
I 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..
View 4 Replies
View Related
Jun 12, 2008
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 Related
Jun 22, 2014
I have a workbook with over 70 tabs whose position shouldn't be changed. Some of these tabs are colored in yellow (sorting by tab color is not allowed). I need to select these yellow tabs first and loop through them (only yellow tabs) and hide empty rows in the range of A1: G 50 on each of them. Grouping sheets wwon't work because each tab has different last row with data within that range.
View 6 Replies
View Related
May 7, 2013
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 Related
Oct 29, 2013
I 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
View 6 Replies
View Related
