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

"Gord Dibben" wrote:

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


On Wed, 17 Dec 2008 01:05:01 -0800, Joanne
wrote:

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" wrote:

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

On Tue, 16 Dec 2008 11:49:01 -0800, Joanne
wrote:

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" wrote:

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

On Tue, 16 Dec 2008 08:04:01 -0800, Joanne
wrote:

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

"Socko" wrote:

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