Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding or showing rows based on a cell value
Is there a way to hide or show rows based whether or not there is a specific
value in a specific cell. For example, if D5=0, then hide Row5. If D5 does not equal 0, then show Row5. Thanks for the help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding or showing rows based on a cell value
Hi
You need to explain a little more. For example at some stage D5 will persumably have nothing in it so it becomes hidden. How do you then intend to put something in it to change that bearing in mind you can't see the cell? The way you intend to do this with affect the solution. Mike "jordanpcpre" wrote: Is there a way to hide or show rows based whether or not there is a specific value in a specific cell. For example, if D5=0, then hide Row5. If D5 does not equal 0, then show Row5. Thanks for the help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding or showing rows based on a cell value
Auto-filter
-- David Biddulph "jordanpcpre" wrote in message ... Is there a way to hide or show rows based whether or not there is a specific value in a specific cell. For example, if D5=0, then hide Row5. If D5 does not equal 0, then show Row5. Thanks for the help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding or showing rows based on a cell value
The cell has a formula in it that is a LOOKUP foruma. So if the LOOKUP
formula has no value, it places 0 in that cell. I would like to be able to hide the rows that come up with 0. Thanks! "Mike H" wrote: Hi You need to explain a little more. For example at some stage D5 will persumably have nothing in it so it becomes hidden. How do you then intend to put something in it to change that bearing in mind you can't see the cell? The way you intend to do this with affect the solution. Mike "jordanpcpre" wrote: Is there a way to hide or show rows based whether or not there is a specific value in a specific cell. For example, if D5=0, then hide Row5. If D5 does not equal 0, then show Row5. Thanks for the help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding or showing rows based on a cell value
Sheet event code...............
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Hide rows with formulas but no data Dim cell As Range Application.ScreenUpdating = False With ActiveSheet.UsedRange .Rows.Hidden = False For Each cell In .Columns(2).SpecialCells(xlCellTypeFormulas) If cell.Text = "" Or cell.Value = 0 Then cell.EntireRow.Hidden = True Next cell End With End Sub Straight macro....................... Sub Hide_Rows_With_Zero() ActiveSheet.Columns(1).EntireRow.Hidden = False FindVal = 0 Set b = Range("B:B").Find(What:=FindVal, LookIn:=xlValues) While Not (b Is Nothing) b.EntireRow.Hidden = True Set b = Range("B:B").Find(What:=FindVal, LookAt:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Wed, 23 Apr 2008 12:24:06 -0700, jordanpcpre wrote: The cell has a formula in it that is a LOOKUP foruma. So if the LOOKUP formula has no value, it places 0 in that cell. I would like to be able to hide the rows that come up with 0. Thanks! "Mike H" wrote: Hi You need to explain a little more. For example at some stage D5 will persumably have nothing in it so it becomes hidden. How do you then intend to put something in it to change that bearing in mind you can't see the cell? The way you intend to do this with affect the solution. Mike "jordanpcpre" wrote: Is there a way to hide or show rows based whether or not there is a specific value in a specific cell. For example, if D5=0, then hide Row5. If D5 does not equal 0, then show Row5. Thanks for the help! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding or showing rows based on a cell value
Would you please explain how I perform the Macro or Event Code below so that
I can hide rows that have a value of 0. For example: G179 has a Lookup formula that came up with a value of 0. I would like to hide row 179 because G179=0. I also want to hide other Rows that have a value of 0 in column G. Thanks for the help. "Gord Dibben" wrote: Sheet event code............... Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Hide rows with formulas but no data Dim cell As Range Application.ScreenUpdating = False With ActiveSheet.UsedRange .Rows.Hidden = False For Each cell In .Columns(2).SpecialCells(xlCellTypeFormulas) If cell.Text = "" Or cell.Value = 0 Then cell.EntireRow.Hidden = True Next cell End With End Sub Straight macro....................... Sub Hide_Rows_With_Zero() ActiveSheet.Columns(1).EntireRow.Hidden = False FindVal = 0 Set b = Range("B:B").Find(What:=FindVal, LookIn:=xlValues) While Not (b Is Nothing) b.EntireRow.Hidden = True Set b = Range("B:B").Find(What:=FindVal, LookAt:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Wed, 23 Apr 2008 12:24:06 -0700, jordanpcpre wrote: The cell has a formula in it that is a LOOKUP foruma. So if the LOOKUP formula has no value, it places 0 in that cell. I would like to be able to hide the rows that come up with 0. Thanks! "Mike H" wrote: Hi You need to explain a little more. For example at some stage D5 will persumably have nothing in it so it becomes hidden. How do you then intend to put something in it to change that bearing in mind you can't see the cell? The way you intend to do this with affect the solution. Mike "jordanpcpre" wrote: Is there a way to hide or show rows based whether or not there is a specific value in a specific cell. For example, if D5=0, then hide Row5. If D5 does not equal 0, then show Row5. Thanks for the help! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding or showing rows based on a cell value
The sheet event code will be pasted into the sheet module.
Right-click on the sheet tab and "View Code". Paste into that module. Alt + q to return to the Excel window. The hiding and unhiding of any row with a formula in Column B that returns a zero will be automatic. Adjust Columns(2) to whatever column you like. (1) is A, (2) is B etc. The Hide_Rows_With_Zero macro which has to be run manually is stored in a general module. Alt + F11 to open the VBE. CTRL + r to open the Project Explorer. Right-click on your workbook/project name and InsertModule. Paste the macro into that module. Edit the B:B to suit Alt + 1 to feturn to the Excel window. You can run the macro by Alt + F8 then select and run or assign the macro to a button or shortcut key combo. Gord On Thu, 24 Apr 2008 07:22:01 -0700, jordanpcpre wrote: Would you please explain how I perform the Macro or Event Code below so that I can hide rows that have a value of 0. For example: G179 has a Lookup formula that came up with a value of 0. I would like to hide row 179 because G179=0. I also want to hide other Rows that have a value of 0 in column G. Thanks for the help. "Gord Dibben" wrote: Sheet event code............... Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Hide rows with formulas but no data Dim cell As Range Application.ScreenUpdating = False With ActiveSheet.UsedRange .Rows.Hidden = False For Each cell In .Columns(2).SpecialCells(xlCellTypeFormulas) If cell.Text = "" Or cell.Value = 0 Then cell.EntireRow.Hidden = True Next cell End With End Sub Straight macro....................... Sub Hide_Rows_With_Zero() ActiveSheet.Columns(1).EntireRow.Hidden = False FindVal = 0 Set b = Range("B:B").Find(What:=FindVal, LookIn:=xlValues) While Not (b Is Nothing) b.EntireRow.Hidden = True Set b = Range("B:B").Find(What:=FindVal, LookAt:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Wed, 23 Apr 2008 12:24:06 -0700, jordanpcpre wrote: The cell has a formula in it that is a LOOKUP foruma. So if the LOOKUP formula has no value, it places 0 in that cell. I would like to be able to hide the rows that come up with 0. Thanks! "Mike H" wrote: Hi You need to explain a little more. For example at some stage D5 will persumably have nothing in it so it becomes hidden. How do you then intend to put something in it to change that bearing in mind you can't see the cell? The way you intend to do this with affect the solution. Mike "jordanpcpre" wrote: Is there a way to hide or show rows based whether or not there is a specific value in a specific cell. For example, if D5=0, then hide Row5. If D5 does not equal 0, then show Row5. Thanks for the help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding/Showing Rows on Cell H2 Change | Excel Discussion (Misc queries) | |||
Hiding rows based value of cell within row | Excel Discussion (Misc queries) | |||
Hiding Specific Rows Based on Values in Other Rows | Excel Worksheet Functions | |||
hiding rows based on cell contents | Excel Discussion (Misc queries) | |||
Hiding rows based on a value | Excel Discussion (Misc queries) |