Hi Guys,

I have a workbook that has around ten sheets in it with a master sheet that
collates results/totals. Data comes in via web queries.

I'm using the Worksheet_Change event to do some calculations and then to
copy 10 rows of data to my master sheet. In the mastersheet I only want to
see the last row of the ten copied as that contains the info I need to see
quickly.

I have been trying with this line of code inside the Worksheet_Change event
but have been getting nothing. Not even an error. However if I run same
line of code via a sub in a module or even in the immediate window it works
fine.

Here's the line...[assume sheet2 has focus, but it's not important]

Sheet1.Range(Cells(Rows.count, "A").End(xlUp).Offset(-1, 0), _
Cells(Rows.count, "A").End(xlUp).Offset(-9, 0)).EntireRow.Hidden = True


I have also tried....

Range(Sheet1.Cells(Rows.count, "A").End(xlUp).Offset(-1, 0), Sheet1 _
..Cells(Rows.count, "A").End(xlUp).Offset(-7, 0)).EntireRow.Hidden _
= True


I've split the code line here for display purposes but it's all one line in
my editor.

Any ideas what I've missed here?

Thanks in advance.

Ron

Code in a sheet always defaults to the sheet that it is in. Code in a module
defaults to the active sheet. As a guess that is where your code is messing
up...

with Sheet1
..Range(.Cells(Rows.count, "A").End(xlUp).Offset(-1, 0), _
..Cells(Rows.count, "A").End(xlUp).Offset(-9, 0)).EntireRow.Hidden = True
end with

Note I have added dots before Cells so that the reference is explicilty to
Sheet1
--
HTH...

Jim Thomlinson


"Ron" wrote:

Hi Guys,

I have a workbook that has around ten sheets in it with a master sheet that
collates results/totals. Data comes in via web queries.

I'm using the Worksheet_Change event to do some calculations and then to
copy 10 rows of data to my master sheet. In the mastersheet I only want to
see the last row of the ten copied as that contains the info I need to see
quickly.

I have been trying with this line of code inside the Worksheet_Change event
but have been getting nothing. Not even an error. However if I run same
line of code via a sub in a module or even in the immediate window it works
fine.

Here's the line...[assume sheet2 has focus, but it's not important]

Sheet1.Range(Cells(Rows.count, "A").End(xlUp).Offset(-1, 0), _
Cells(Rows.count, "A").End(xlUp).Offset(-9, 0)).EntireRow.Hidden = True


I have also tried....

Range(Sheet1.Cells(Rows.count, "A").End(xlUp).Offset(-1, 0), Sheet1 _
..Cells(Rows.count, "A").End(xlUp).Offset(-7, 0)).EntireRow.Hidden _
= True


I've split the code line here for display purposes but it's all one line in
my editor.

Any ideas what I've missed here?

Thanks in advance.

Ron

Hi Jim,

Just the ticket. Worked a treat, thanks a lot.

Ron




wrote in
:

Code in a sheet always defaults to the sheet that it is in. Code in a
module defaults to the active sheet. As a guess that is where your
code is messing up...

with Sheet1
.Range(.Cells(Rows.count, "A").End(xlUp).Offset(-1, 0), _
.Cells(Rows.count, "A").End(xlUp).Offset(-9, 0)).EntireRow.Hidden =
True end with

Note I have added dots before Cells so that the reference is
explicilty to Sheet1