Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto hide
Is there a way to auto hide a row based on the contents of a certain column
in the row? For Example: Auto hide any row where column A has a - for the value of the cell. I believe value is the right term here because the reason I ask the cell will contain a formula that may result in a - if so I want the row hidden. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto hide
The easiest way is autofilter.
-- David Biddulph "Chad" wrote in message ... Is there a way to auto hide a row based on the contents of a certain column in the row? For Example: Auto hide any row where column A has a - for the value of the cell. I believe value is the right term here because the reason I ask the cell will contain a formula that may result in a - if so I want the row hidden. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto hide
On Jan 30, 11:26*am, Chad wrote:
Is there a way to auto hide a row based on the contents of a certain column in the row? For Example: Auto hide any row where column A has a - for the value of the cell. I believe value is the right term here because the reason I ask the cell will contain a formula that may result in a - if so I want the row hidden.. Hi, You can have control in place like textbox which can be linked to the cell (e.g. A1). To link use textbox property "Linkedcell" and below textbox event will do the rest. Example: Private Sub TextBox1_Change() Select Case TextBox1.Value Case 1 Range("a2:a5").EntireRow.Hidden = True Range("a6:a10").EntireRow.Hidden = False Range("a11:a15").EntireRow.Hidden = False Case 2 Range("a6:a10").EntireRow.Hidden = True Range("a2:a5").EntireRow.Hidden = False Range("a11:a15").EntireRow.Hidden = False Case 3 Range("a11:a15").EntireRow.Hidden = True Range("a2:a5").EntireRow.Hidden = False Range("a6:a10").EntireRow.Hidden = False Case Else Range("a2:a5").EntireRow.Hidden = False Range("a6:a10").EntireRow.Hidden = False Range("a11:a15").EntireRow.Hidden = False End Select End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto hide
Private Sub Worksheet_Calculate()
'Hide rows with formulas that return negatives Dim cell As Range On Error GoTo endit Application.EnableEvents = False Application.ScreenUpdating = False With Me.UsedRange .Rows.Hidden = False For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas) If cell.Value < 0 Then cell.EntireRow.Hidden = True Next cell End With endit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Thu, 29 Jan 2009 22:26:01 -0800, Chad wrote: Is there a way to auto hide a row based on the contents of a certain column in the row? For Example: Auto hide any row where column A has a - for the value of the cell. I believe value is the right term here because the reason I ask the cell will contain a formula that may result in a - if so I want the row hidden. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto hide
"Gord Dibben" wrote: Private Sub Worksheet_Calculate() 'Hide rows with formulas that return negatives Dim cell As Range On Error GoTo endit Application.EnableEvents = False Application.ScreenUpdating = False With Me.UsedRange .Rows.Hidden = False For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas) If cell.Value < 0 Then cell.EntireRow.Hidden = True Next cell End With endit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Ok I think this is the macro I need to run however I am just now learning macros and when I copy and pasted of course it did not work for the fact you do not have the names of the sheets in my file. Is there anyway I can get you to repost this macro with each part I need to replace with file specific info being follow by a set of (). For example if something on here needs the name of a certain sheet follow it with like (sheet1) or something like that or link me a site to teach me macros. /VR |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto hide
This is worksheet event code which runs whenever a calculation takes place.
Sheet names don't matter. To place the code in one sheet just right-click on the sheet tab and "View Code" Paste the code into that sheet module. If you want the code to run on every sheet in the workbook, change the first line to Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If using Excel 2003 or earlier............Right-click on the Excel Icon left of "File" on the Menu Bar or left end of the title bar if window is not maximized. Select "View Code" and paste into Thisworkbook module. If using Excel 2007, Alt + F11 to go to Visual Basic Editor. Expand Excel Objects and double-click on Thisworkbook module. Paste into that module. For more on this and Event Code, see Ron de Bruin's site which has instructions and links to other sites. http://www.rondebruin.nl/code.htm Gord On Fri, 30 Jan 2009 22:17:01 -0800, Chad wrote: "Gord Dibben" wrote: Private Sub Worksheet_Calculate() 'Hide rows with formulas that return negatives Dim cell As Range On Error GoTo endit Application.EnableEvents = False Application.ScreenUpdating = False With Me.UsedRange .Rows.Hidden = False For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas) If cell.Value < 0 Then cell.EntireRow.Hidden = True Next cell End With endit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Ok I think this is the macro I need to run however I am just now learning macros and when I copy and pasted of course it did not work for the fact you do not have the names of the sheets in my file. Is there anyway I can get you to repost this macro with each part I need to replace with file specific info being follow by a set of (). For example if something on here needs the name of a certain sheet follow it with like (sheet1) or something like that or link me a site to teach me macros. /VR |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto hide
When I follow those steps it keeps giving me errors after the with me part.
(.UsedRange ..Rows.Hidden = False) That is the exact part it is having issue with. Any ideas again thanks for your help |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto hide
Where are you placing the code?
Worksheet only............original code should work fine. If you placed it in Thisworkbook module, I screwed up a couple of things.............apologies. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) should have been Private Sub Workbook_SheetCalculate(ByVal Sh As Object) And we can't use Me.UsedRange in Thisworkbook since Me refers to ActiveSheet. Here is revised code to go into Thisworkbook module Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 'Hide rows with formulas that return negatives Dim cell As Range On Error GoTo endit Application.EnableEvents = False Application.ScreenUpdating = False With ActiveSheet.UsedRange .Rows.Hidden = False For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas) If cell.Value < 0 Then cell.EntireRow.Hidden = True Next cell End With endit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Gord On Sat, 31 Jan 2009 08:56:01 -0800, Chad wrote: When I follow those steps it keeps giving me errors after the with me part. (.UsedRange .Rows.Hidden = False) That is the exact part it is having issue with. Any ideas again thanks for your help |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto hide
Gord
While doing search for help, found this post which is basically the question we have and wonder if you might be able to help. Understand (from a previous post in this newsgroup) it is not possible to write a formula to hide a row when a cell in that row has a certain value, like the word 'Done'. But, am not sure how your code is supposed to be used/modified (after pasting in the code interface page) in order to make that happen with code. Not that familiar with Excel 'code'. Checked out the link... http://www.rondebruin.nl/code.htm ....but didnt find much help. Could you provide a specific example* of the code (and related formula ?) for the example* above ? *Hide row when cell in row has the word text 'Done' (no quotes) typed in... and is it possible to then 'un-hide' the cell with the normal right-click command ? -- Thanks again for your help. OS: XP, SP-2 (Office2003) "Gord Dibben" wrote: Where are you placing the code? Worksheet only............original code should work fine. If you placed it in Thisworkbook module, I screwed up a couple of things.............apologies. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) should have been Private Sub Workbook_SheetCalculate(ByVal Sh As Object) And we can't use Me.UsedRange in Thisworkbook since Me refers to ActiveSheet. Here is revised code to go into Thisworkbook module Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 'Hide rows with formulas that return negatives Dim cell As Range On Error GoTo endit Application.EnableEvents = False Application.ScreenUpdating = False With ActiveSheet.UsedRange .Rows.Hidden = False For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas) If cell.Value < 0 Then cell.EntireRow.Hidden = True Next cell End With endit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Gord On Sat, 31 Jan 2009 08:56:01 -0800, Chad wrote: When I follow those steps it keeps giving me errors after the with me part. (.UsedRange .Rows.Hidden = False) That is the exact part it is having issue with. Any ideas again thanks for your help |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto hide
This is perfect and works great for me. However I need to protect my sheet
and when I do the code does not work. I make it so that users can format rows and cells but still it ceases to work. Can I get this to work on protected sheets? "Gord Dibben" wrote: Private Sub Worksheet_Calculate() 'Hide rows with formulas that return negatives Dim cell As Range On Error GoTo endit Application.EnableEvents = False Application.ScreenUpdating = False With Me.UsedRange .Rows.Hidden = False For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas) If cell.Value < 0 Then cell.EntireRow.Hidden = True Next cell End With endit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Thu, 29 Jan 2009 22:26:01 -0800, Chad wrote: Is there a way to auto hide a row based on the contents of a certain column in the row? For Example: Auto hide any row where column A has a - for the value of the cell. I believe value is the right term here because the reason I ask the cell will contain a formula that may result in a - if so I want the row hidden. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto hide/Unhide columns | Excel Worksheet Functions | |||
Auto Hide Columns when... | Excel Discussion (Misc queries) | |||
Auto Hide Columns | Excel Discussion (Misc queries) | |||
Auto Hide Columns & Rows | Excel Discussion (Misc queries) | |||
AUTO HIDE ROWS | Excel Worksheet Functions |