Worksheet_Change event problem
 

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

Worksheet_Change event problem
 

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

Worksheet_Change event problem
 

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