auto hide

[Chad]

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.

 

[Nyatiaju]

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

 

[Gord Dibben]

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

 

[Chad]

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

 

[Gord Dibben]

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

 

[Chad]

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

 

[Gord Dibben]

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

 

[akm]

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 ?

 

[finkeldp]

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?