Problem define range for multiple sheets

[Guest]

Hi all,

I am trying to develop a routine for hiding rows that can be used on all
sheets. In Sheet 1, which is the Master sheet, I count the number of rows
that I can hide. The code when applied to Sheet 1 works.

When I try to apply it to sheet 2 it fails. It fails because the "
lRowsCount " counts the ActiveSheet selected rows. Can someone help to get
the range defined correctly so that it can be applied to all sheets.

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

'Count number of rows to hide from Sheet 1 - Code fails on Sheet 2 as it is
counting the ActiveSheet Selection which in this example is Sheet 2
lRowsCount = Selection.Rows.Count - 1

' Apply number of Rows to hide in Sheet 2
ActiveSheet.Range("ptrMinMaxWageScale", _
Range("ptrMinMaxWageScale").Offset(-lRowsCount, 0)).Select

Selection.EntireRow.Hidden = True

Many thanks in advance/sgl

 

[merjet]

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

 

[Guest]

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]

Another try.

Change: lRowsCount = Selection.Rows.Count - 1
To: lRowsCount = Sheets("Sheet1").Rows.Count - 1

Hth,
Merjet

 

[Guest]

'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.

 

[Guest]

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