Problem with conditional hide/unhide row macro

[Joanne]

Hello all

I'm certainly no expert, and I'm trying to botch my way through this
project. Surprise, surprise I got stuck again
I found a macro on the web:

http://excel.tips.net/Pages/T001940_Hiding_Rows_Based_on_a_Cell_Value.html

I adjusted it a bit, but it's still not working, can anyone help? I'm
looking for a macro that will hide/unhide rows depending on whether the value
in the N column is "Yes", or "-". If "Yes" then show, if "-" then hide. The
value could change so the macro needs to be able to handle that as well.

Thanks in advance

Sub HideRows()
BeginRow = 51
EndRow = 1354
ChkCol = 14

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "-" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

 

[Socko]

Since you wanted the flexibility of macro to handle any change as
well, the following code will take care of it. Please insert the
following code to the worksheet codemodule of a given worksheet.
(please be advised that this code will work only if there is any
change in the content of a cell... if the cell links to another cell
and if the related cell is changed, this code wont help).,


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Then
Call HideRows
End If
End Sub


I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, and other resources
Please visit: http://socko.wordpress.com

 

[Joanne]

Socko

Thanks for your help.
I pasted in this macro, but it doesn't seem to work. On the debugger, it has
a problem with "If Target.Column = 14 Then", the error "object required"
comes up.

But even so, the cell that will change is linked to another cell and is NOT
modified manually. It would be a specific change not 'any', so it wouldn't be
the right macro anyway.

Any other suggestions would be very much appreciated

 

[Gord Dibben]

Your HideRows macro works for me but maybe you want it to run automatically
when values change in column 14?

Right-click on the sheet tab and "View Code". Copy/paste this event code
into that sheet module.

Private Sub Worksheet_Calculate()
BeginRow = 51
EndRow = 1354
ChkCol = 14
Application.EnableEvents = False
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "-" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Joanne]

Gord

Thank you for your help

I have copied your code into the sheet module, however it still doesn't seem
to work. Neither when the ChkCol is changed, or when I try to run the macro.
When I try to run the macro there is a problem with the line 'If
Cells(RowCnt, ChkCol).Value = "-" Then'.

Could the problem be that at the moment I am testing this macro when there
is little data entry, so most of the values start out (before data is
entered) as error values such as #N/A?

 

[Gord Dibben]

This is not a macro to be run by you..............it is sheet event code and
runs when calculation takes place.

You said the cells with values of "-" in column 14 were formula-generated.

When the value changes from "-" to something else because of a calculation,
the row(s) will unhide.

Is the cell value literally "-" or just a -

If "-" change the code to read If Cells(RowCnt, ChkCol).Value = ""-"" Then

If you want to send me a de-sensitized copy of your workbook with a detailed
explantion of what you want to happen, I will see what I can do.

I can't ell from the posts so far exactly what you need done.

Change the AT and DOT in my email address to send to me.


Gord

 

[Bill H]

Gord,

I am trying to do something almost identical to Joanne, but am not having
any luck with your code.

I modified it to:


Private Sub Worksheet_Calculate()
BeginRow = 1
EndRow = 1
ChkCol = 1
Application.EnableEvents = False
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "1" Then
Cells(RowCnt + 1, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt + 1, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
Application.EnableEvents = True
End Sub


I type the value 1 in cell, A1, but row 2 does not hide.

I also changed the if to look for 1 rather than "1", but there was no change.

Do we need to do something special to activate this code ?

Thanks
Bill H

 

[Gord Dibben]

Works for me as you have it written but requires Calc mode to be on
Automatic.

If you are just typing the 1 in A1 you should be using

Private Sub Worksheet_Change(ByVal Target As Range)

instead of Private Sub Worksheet_Calculate()


Gord

 

[Bill H]

Perfect, thanks !

I've got it working

Bill H

 

[Bill H]

Gord,

THanks for all the help, I've got it working now !

Just a side question, can I reference a cell on another work sheet, or by a
named cell ?

Thanks
Bill H

 

[Gord Dibben]

To run some event code or???

I would say most likely

Give some details


Gord

 

[Bill H]

Same problem.

But instead of using
If Cells(RowCnt, ChkCol).Value = "-"
can I refer to a cell in another sheet by naming it ?

If so, what would the code look like.

THanks
BIll H

 

[Dave Peterson]

if worksheets("somesheetnamehere").range("a1").value > 999 then
or
if worksheets("somesheetnamehere").range("somenamehere").value > 999 then

 

[Joanne]

Gord

I do realise it should run when calculations are made. It didn't work when a
change was made and the calculation was done. In my hope to get it working I
thought I'd try running it anyway.

Sorry if I've not been very clear in my explanations. The formula in the N
column (ChkCol) is linked to another worksheet. When the workbook is blank
the N column shows a value of #REF!. Depending on data entry in other
worksheets this cell will change to Yes or -. (No it is not literally "-"). I
only want the row to be displayed if the cell contains "Yes". So if it
contains - it should be hiden.

The macro does seem to work, when the value changes from "Yes" to "-". But
although that may happen when the user does data entry, it will not be the
most common occurence.

Maybe the best solution would be to hide all rows and set the macro to
unhide the row if the value is changed to "Yes". If this a more effective
method, how would the code look, my guess was as follows, but it didn't work

Thanks for patience and your help

Sub HideRows()
BeginRow = 51
EndRow = 1354
ChkCol = 14
Application.EnableEvents = False
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "Yes" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
Application.EnableEvents = True
End Sub

 

[Gord Dibben]

You are mixing up the two sets of code in your latest example.

Do you want the rows to be hidden automatically or by manually running the
code?

The Worksheet_Calculate code in the sheet module should hide the rows
automatically when the value changes to - in any cell in column N and unhide
on any other value.

Column N has formulas that derive their results from cell values in another
sheet............right?

I don't understand why they would show #REF! when the source cells are
blank.

Please post a few examples of the formulas in column N and we can work from
there.

Otherwise send me the workbook to look at.


Gord

 

[Joanne]

Gord

I have sent you a de-sensitised version of my workbook to:
gorddibbATshawDOTca.
There is a full explanation in the email.

Thanks in advance

 

[JP Meyer]

Is there an easy way to set this to run only on the active worksheet?

I found this method extremely useful, but when I run a multi-tabbed
workbook, Excel runs the code for all tabs every time I make any update in
any single sheet. It even runs it every time updates are made in other
workbooks.