Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem define range for multiple sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem define range for multiple sheets
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem define range for multiple sheets
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem define range for multiple sheets
Another try.
Change: lRowsCount = Selection.Rows.Count - 1 To: lRowsCount = Sheets("Sheet1").Rows.Count - 1 Hth, Merjet |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem define range for multiple sheets
'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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem define range for multiple sheets
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Define Name - same name for multiple cells on multiple sheets | Excel Worksheet Functions | |||
Print Multiple Sheets to PDF Problem | Excel Discussion (Misc queries) | |||
Sum a range across multiple sheets | Excel Worksheet Functions | |||
problem using Set statement to define range object | Excel Programming | |||
Problem copying range and pasting to multiple sheets | Excel Programming |