'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