Thank you both for your assistance. I have chosen Toms option which works
fine for me. As a matter of intetrest Tom "ptrMinMaxWageScale" does refer to
a single cell. How can I make this simpler?

Many thanks/sgl

"Tom Ogilvy" wrote:

'Count number of rows to hide from Sheet 1
Set wksWS1 = Worksheets("Sheet1")
With wksWS1
Set rngRows = .Range("ptrMinMaxWageScale", _
.Range("ptrMinMaxWageScale") _
.End(xlUp).Offset(1, 0))
End With

'Count number of rows to hide from Sheet 1
lRowsCount = rngRows.Rows.Count - 1

' Apply number of Rows to hide in Sheet 2
With Activesheet
.Range("ptrMinMaxWageScale", _
.Range("ptrMinMaxWageScale")) _
.Offset(-lRowsCount, 0)) _
.EntireRow.Hidden = True
End With


if ptrMinMaxWageScale refers to a single cell, you could make this simpler.


--
Regards,
Tom Ogilvy



"sgl" wrote:

Merjet thanks for your reply.
Perhaps I have not put my problem exactly right. I have 3 sheets with common
layout. The Defined Name "ptrMinMaxWageScale" is defined locally in each
worksheet. There is only one - Macro1 - that runs in any one of these sheets.

There is a button to run the Macro 1 in each one of the sheets. So when you
are in any one of the Sheets it becomes an activesheet. Activating Macro1 in
the active sheet should hide the rows in the active sheet only.

However, the number of rows to hide can only be counted in Sheet 1. I
therefore need to count the number of Rows to hide in Sheet 1 which the
Macro1 will use to hide the rows in Sheets 2+3.

I hope this is clear now/thanks/sgl

"merjet" wrote:

I'm not real clear on what you are trying to do, but you don't have to
activate or select Sheet2 in order to hide some rows on it.

Sheets("Sheet2").Range("ptrMinMaxWageScale", _
Range("ptrMinMaxWageScale").Offset(-lRowsCount, 0)) _
.EntireRow.Hidden = True

Hth,
Merjet