VBA To Hide Rows Based On Cell Content

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


ADVERTISEMENT

Hide Columns Based On Cell Content

Apr 3, 2008

I'm sure this is an easy one, but I have never done this before. I need to know the best way to hide (for example) Columns U:W if cell U3 is blank.

View 9 Replies View Related

Deleting Rows Based On Cell Content

Oct 17, 2007

I wish to remove the page headers that were imported along with the raw data. Here's the code I've been using.

Column_To_Check = 3
Start_Row = 1
End_Row = ActiveSheet. Cells(Start_Row, Column_To_Check).CurrentRegion.Rows.Count
MsgBox End_Row
Search_String = "."
For Row_Counter = Start_Row To End_Row
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value <> Search_String Then
ActiveSheet.Rows(Row_Counter).Delete
Row_Counter = Row_Counter - 1
End If
Next Row_Counter
End Sub

When I import the data from the text file, there are 3 dimensions separated by a period in the same field. I.E: ABC.DEF.GHI and I use the break points to seperate as such: ABC|.|DEF|.|GHI|. Therefore, all rows that contain data also contain a period in cell C, but headers don't.

The Problem

End_Row = ActiveSheet.Cells(Start_Row, Column_To_Check).CurrentRegion.Rows.Count

CurrentRegion only selects up to the first blank row. However, I need this to select the whole sheet, not just the current region.

View 9 Replies View Related

Excel 2003 :: Deleting Or Displaying Rows Based On Cell Content One By One

Mar 20, 2014

I have a company with upto 5 products, and I have created a dropdown box where you select your product.

When I select product 1 i would need info on product 1 , when i select product 2 I need info on product 1 and 2 and continue. so for product 5 -i need info on product 1-5.

I tried to add all info on excel and tried to delete it one by one by deleting rows in excel for products but it does not work.

View 4 Replies View Related

Hide Rows Based On Value In Cell G2

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

VBA Hide Rows Based On Cell Value

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

Hide Rows Based Upon Value In Cell?

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

Hide Rows Based On Cell Value Inputted From Another Tab

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

How To Hide/unhide Rows Based On Cell Value

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

Macro To Hide Rows Based On Cell Value

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

Auto Hide Rows Based On Cell Value In Same Row

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

Hide/Unhide Rows Based On 1 Cell

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

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 View Related

Hide Rows Based On Two Cell Values

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

Hide Rows Based On A Cell Reference

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

Hide & Unhide Rows Based On Cell Value

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

Hide Or Filter Rows Based On Cell Value

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

Auto Hide Rows Based On Cell Above

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

Conditionally Hide Rows Based On Cell Value

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

Hide Rows Based On Other Cell Values

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

Hide Rows Based On Value In Specific Cell

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

Hide & Show Rows Based On Cell Value

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

Hide Rows Based On Cell Value In Column

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

Change Content Of One Cell Based On Content Of Another

Apr 18, 2014

Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.

Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"

I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).

View 4 Replies View Related

Automatically Hide / Show Rows Based On Cell Value

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

Open Another Sheet And Hide Rows Based On Cell Value?

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

Hide / Unhide Rows Based On Cell Value Containing Formula

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

Hide Range Of Rows Based On Cell Values

Aug 13, 2014

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] ....

View 6 Replies View Related

Hide Multiple Rows Based On Cell Value Being Blank

Feb 20, 2013

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.

View 2 Replies View Related

Hide / Unhide Rows Based On Calculated Cell?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved