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_...ell_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

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

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

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

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

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