code failing in hidden rows

[RobN]

I have the following code as part of other code which is executed via a
UserForm.
With Sheet4
.Range("V13:V20").Value = .Range("R5:R12").Value
End With

When I unhide those rows the code works fine.

Part of the range, namely rows 16 to 23 are hidden. When I execute this
code, those cells located within the hidden range do not reflect the values
from the R5:R12 range. No error message appears, yet only values are copied
into the unhidden cells.
Other parts of the code within this procedure do copy the value to the cells
in the hidden range, eg....
If Range("U9") = True Then Range("V17") = CloseBal

Can anyone see a problem here?

Rob

 

[JLatham]

How is Sheet4 defined?
If you're actually working with a sheet named 'Sheet4' then I'd expect to
see code ahead of the With statement something like this:
Dim Sheet4 As Worksheet
Set Sheet4 = Worksheets("realWorksheetName") ' change for real world

When I do that, things work fine for me in XL 2003, whether column V is
hidden or not.

Also in your If test, you're just using Range(), not the .Range property of
a specific sheet, which would have you looking at the active sheet.

 

[Dave Peterson]

I couldn't duplicate the problem in xl2003.

What version of excel are you using? Maybe someone with that version could try
to duplicate it.

 

[Dave Peterson]

I bet Rob is using the codename so he doesn't have to worry about any user
changing the name on the worksheet tab.

 

[RobN]

Dave,

I'm using the dreaded 2007 version, which is giving me grief in other areas
as well...GRRRrrrr!! I'm certainly not recommending anyone use this version
at all.

Rob

 

[RobN]

I haven't defined the sheet with Dim and Set statements as you suggest.
When I try that it doesn't make any difference.
I'm not sure what you mean with the IF test. That line works fine as it is.

BTW, it's the ROWS that are hidden and not the COLUMN. So Range("V13:V20")
has some hidden and some unhidden rows. When I run the procedure, the values
are copied OK to the unhidden part of the range, but skips the hidden part,
so I don't think it has anything to do with naming and active sheet, etc.

As I told Dave, I'm using Vs 2007.

Rob

 

[RobN]

Dave,

Yes, absolutely right! Even if the user is me as it saves a whole bunch of
time changing all the names within all the procedures.

Rob

 

[JLatham]

Ok, I'll try setting things up a little differently in 2007 and see what
happens. Can't do it right now - business meeting to attend that will run
late, but I'll give it a shot later this evening. I'll presume that since it
works when rows are not hidden that there's not a problem with the definition
of Sheet4, and just focus on the copying of values - and whether or not they
work on my 2007 system.

Dave,

I'm using the dreaded 2007 version, which is giving me grief in other areas
as well...GRRRrrrr!! I'm certainly not recommending anyone use this version
at all.

Rob

 

[Dave Peterson]

I opened xl2007 (not visual studio!) and tested your code.

It worked fine.

Could you have some sort of event macro running, too?

ps. I hid the rows manually.

 

[RobN]

Dave,

Yes, this is a hugely complicated workbook with Worksheet_Change and
Worksheet_SelectionChange on that sheet.

I could post all of the sheet's code here, and also the full code from the
UserForm which includes the code in question, but there are also numerous
Modules to which the sheet code refers, and providing all that to really nut
out what could cause the problem may be rather tedious.

I suspect I'm just having a glitch with this and that there isn't actually
an error in the code at all. As you have confirmed, the code works fine.

I must point out, and maybe this may have a cause, is that I'm running this
file in [Compatibility Mode] so that others not having 2007 can have acces
to it. I have tried to save as an XLSM file to test if the problem still
occurs when running as 2007 version, but even though it saves OK, I can't
run any macros, even though they are there! It says they are disabled.
However, checking the Macro security, I find it is set to "Enable all
macros".

I'm out of ideas, except that I think I'll just transfer the Range to an
area that can remain unhidden.

If you have any help regarding the macro disabled problem, that would also
be appreciated.

Rob

 

[RobN]

Thanks for that. Please see my reply to Dave as that may give you a bit
more insight to my problem.

Rob

Ok, I'll try setting things up a little differently in 2007 and see what
happens. Can't do it right now - business meeting to attend that will run
late, but I'll give it a shot later this evening. I'll presume that since
it
works when rows are not hidden that there's not a problem with the
definition
of Sheet4, and just focus on the copying of values - and whether or not
they
work on my 2007 system.

 

[Dave Peterson]

If you want to avoid whatever event is firing (I don't know if that's a good
idea, though):

application.enableevents = false
With Sheet4
.Range("V13:V20").Value = .Range("R5:R12").Value
End With
application.enableevents = true

(I don't think it has anything to do with compatibility issues.)

Heck, it won't take long to test this--even if you discard it later!

Dave,

Yes, this is a hugely complicated workbook with Worksheet_Change and
Worksheet_SelectionChange on that sheet.

I could post all of the sheet's code here, and also the full code from the
UserForm which includes the code in question, but there are also numerous
Modules to which the sheet code refers, and providing all that to really nut
out what could cause the problem may be rather tedious.

I suspect I'm just having a glitch with this and that there isn't actually
an error in the code at all. As you have confirmed, the code works fine.

I must point out, and maybe this may have a cause, is that I'm running this
file in [Compatibility Mode] so that others not having 2007 can have acces
to it. I have tried to save as an XLSM file to test if the problem still
occurs when running as 2007 version, but even though it saves OK, I can't
run any macros, even though they are there! It says they are disabled.
However, checking the Macro security, I find it is set to "Enable all
macros".

I'm out of ideas, except that I think I'll just transfer the Range to an
area that can remain unhidden.

If you have any help regarding the macro disabled problem, that would also
be appreciated.

Rob

 

[RobN]

Thanks for your efforts Dave. I tried it with the events disabled and still
no go.

I find it really odd that part of the code within the same procedure copies
a value successfully to one of the hidden rows, namely, If Range("U9") =
True Then Range("V17") = CloseBal.
Could it be that it doesn't like doing multiple cells particularly a mix of
hidden and unhidden?

Rob

If you want to avoid whatever event is firing (I don't know if that's a
good
idea, though):

application.enableevents = false
With Sheet4
.Range("V13:V20").Value = .Range("R5:R12").Value
End With
application.enableevents = true

(I don't think it has anything to do with compatibility issues.)

Heck, it won't take long to test this--even if you discard it later!

Dave,

Yes, this is a hugely complicated workbook with Worksheet_Change and
Worksheet_SelectionChange on that sheet.

I could post all of the sheet's code here, and also the full code from
the
UserForm which includes the code in question, but there are also numerous
Modules to which the sheet code refers, and providing all that to really
nut
out what could cause the problem may be rather tedious.

I suspect I'm just having a glitch with this and that there isn't
actually
an error in the code at all. As you have confirmed, the code works fine.

I must point out, and maybe this may have a cause, is that I'm running
this
file in [Compatibility Mode] so that others not having 2007 can have
acces
to it. I have tried to save as an XLSM file to test if the problem still
occurs when running as 2007 version, but even though it saves OK, I can't
run any macros, even though they are there! It says they are disabled.
However, checking the Macro security, I find it is set to "Enable all
macros".

I'm out of ideas, except that I think I'll just transfer the Range to an
area that can remain unhidden.

If you have any help regarding the macro disabled problem, that would
also
be appreciated.

Rob

I opened xl2007 (not visual studio!) and tested your code.

It worked fine.

Could you have some sort of event macro running, too?

ps. I hid the rows manually.

RobN wrote:

Dave,

I'm using the dreaded 2007 version, which is giving me grief in other
areas
as well...GRRRrrrr!! I'm certainly not recommending anyone use this
version
at all.

Rob

I couldn't duplicate the problem in xl2003.

What version of excel are you using? Maybe someone with that
version
could try
to duplicate it.

RobN wrote:

I have the following code as part of other code which is executed
via
a
UserForm.
With Sheet4
.Range("V13:V20").Value = .Range("R5:R12").Value
End With

When I unhide those rows the code works fine.

Part of the range, namely rows 16 to 23 are hidden. When I execute
this
code, those cells located within the hidden range do not reflect
the
values
from the R5:R12 range. No error message appears, yet only values
are
copied
into the unhidden cells.
Other parts of the code within this procedure do copy the value to
the
cells
in the hidden range, eg....
If Range("U9") = True Then Range("V17") = CloseBal

Can anyone see a problem here?

Rob

 

[JLatham]

Rob,
I was going to make a suggestion, but Dave made it first - wrapping the
particular section of code in the Disable/Enable events statements. I can
see where the Worksheet_Change would fire, but that code doesn't look like it
should trigger the _SelectionChange event.

Let me ask this - what type of computer (what CPU) are you using? I do know
that there are some issues with multi-core CPUs when performing some
operations within VBA, but I can't be more specific than that: I only know of
one particular situation involving a UDF and a particular VBA function, and
your code isn't a UDF and it's not using the command I know about. And that
issue just causes a delay in results, not total misbehavior.

Another question, kind of related to the macro enabled/disabled problem: You
say you're running this in 2007 and [Compatibility Mode] - what version of
Excel was actually used to create it? I don't have specific ideas at the
moment - just trying to gather info for others that may read all of this and
maybe ring a bell for them.

Thanks for your efforts Dave. I tried it with the events disabled and still
no go.

I find it really odd that part of the code within the same procedure copies
a value successfully to one of the hidden rows, namely, If Range("U9") =
True Then Range("V17") = CloseBal.
Could it be that it doesn't like doing multiple cells particularly a mix of
hidden and unhidden?

Rob

If you want to avoid whatever event is firing (I don't know if that's a
good
idea, though):

application.enableevents = false
With Sheet4
.Range("V13:V20").Value = .Range("R5:R12").Value
End With
application.enableevents = true

(I don't think it has anything to do with compatibility issues.)

Heck, it won't take long to test this--even if you discard it later!

Dave,

Yes, this is a hugely complicated workbook with Worksheet_Change and
Worksheet_SelectionChange on that sheet.

I could post all of the sheet's code here, and also the full code from
the
UserForm which includes the code in question, but there are also numerous
Modules to which the sheet code refers, and providing all that to really
nut
out what could cause the problem may be rather tedious.

I suspect I'm just having a glitch with this and that there isn't
actually
an error in the code at all. As you have confirmed, the code works fine.

I must point out, and maybe this may have a cause, is that I'm running
this
file in [Compatibility Mode] so that others not having 2007 can have
acces
to it. I have tried to save as an XLSM file to test if the problem still
occurs when running as 2007 version, but even though it saves OK, I can't
run any macros, even though they are there! It says they are disabled.
However, checking the Macro security, I find it is set to "Enable all
macros".

I'm out of ideas, except that I think I'll just transfer the Range to an
area that can remain unhidden.

If you have any help regarding the macro disabled problem, that would
also
be appreciated.

Rob

I opened xl2007 (not visual studio!) and tested your code.

It worked fine.

Could you have some sort of event macro running, too?

ps. I hid the rows manually.

RobN wrote:

Dave,

I'm using the dreaded 2007 version, which is giving me grief in other
areas
as well...GRRRrrrr!! I'm certainly not recommending anyone use this
version
at all.

Rob

I couldn't duplicate the problem in xl2003.

What version of excel are you using? Maybe someone with that
version
could try
to duplicate it.

RobN wrote:

I have the following code as part of other code which is executed
via
a
UserForm.
With Sheet4
.Range("V13:V20").Value = .Range("R5:R12").Value
End With

When I unhide those rows the code works fine.

Part of the range, namely rows 16 to 23 are hidden. When I execute
this
code, those cells located within the hidden range do not reflect
the
values
from the R5:R12 range. No error message appears, yet only values
are
copied
into the unhidden cells.
Other parts of the code within this procedure do copy the value to
the
cells
in the hidden range, eg....
If Range("U9") = True Then Range("V17") = CloseBal

Can anyone see a problem here?

Rob

 

[RobN]

Hi Dave,

Failing any other suggestions, I've found a workaround.

Firstly, it seems it may be because I am running this in Compatability Mode.
When hiding Rows or Columns in this mode, the Row or Column sizes are
reduced to 0. This means that when you want to unhide, you also need to
apply a size value of greater than .5 to view those Rows/Columns. In my
testing I find that simply reducing the size of the rows to 0 and not
actually manually hiding them, also produces the problem. Could it be that
the code won't work if the row size is 0? Is that also the case with you?
What about Vs2003?

Instead of hiding those rows, I formatted their height to just 0.75, which
basically hides them enough for my purposes. This fixes the problem for me.

Rob

If you want to avoid whatever event is firing (I don't know if that's a
good
idea, though):

application.enableevents = false
With Sheet4
.Range("V13:V20").Value = .Range("R5:R12").Value
End With
application.enableevents = true

(I don't think it has anything to do with compatibility issues.)

Heck, it won't take long to test this--even if you discard it later!

Dave,

Yes, this is a hugely complicated workbook with Worksheet_Change and
Worksheet_SelectionChange on that sheet.

I could post all of the sheet's code here, and also the full code from
the
UserForm which includes the code in question, but there are also numerous
Modules to which the sheet code refers, and providing all that to really
nut
out what could cause the problem may be rather tedious.

I suspect I'm just having a glitch with this and that there isn't
actually
an error in the code at all. As you have confirmed, the code works fine.

I must point out, and maybe this may have a cause, is that I'm running
this
file in [Compatibility Mode] so that others not having 2007 can have
acces
to it. I have tried to save as an XLSM file to test if the problem still
occurs when running as 2007 version, but even though it saves OK, I can't
run any macros, even though they are there! It says they are disabled.
However, checking the Macro security, I find it is set to "Enable all
macros".

I'm out of ideas, except that I think I'll just transfer the Range to an
area that can remain unhidden.

If you have any help regarding the macro disabled problem, that would
also
be appreciated.

Rob

I opened xl2007 (not visual studio!) and tested your code.

It worked fine.

Could you have some sort of event macro running, too?

ps. I hid the rows manually.

RobN wrote:

Dave,

I'm using the dreaded 2007 version, which is giving me grief in other
areas
as well...GRRRrrrr!! I'm certainly not recommending anyone use this
version
at all.

Rob

I couldn't duplicate the problem in xl2003.

What version of excel are you using? Maybe someone with that
version
could try
to duplicate it.

RobN wrote:

I have the following code as part of other code which is executed
via
a
UserForm.
With Sheet4
.Range("V13:V20").Value = .Range("R5:R12").Value
End With

When I unhide those rows the code works fine.

Part of the range, namely rows 16 to 23 are hidden. When I execute
this
code, those cells located within the hidden range do not reflect
the
values
from the R5:R12 range. No error message appears, yet only values
are
copied
into the unhidden cells.
Other parts of the code within this procedure do copy the value to
the
cells
in the hidden range, eg....
If Range("U9") = True Then Range("V17") = CloseBal

Can anyone see a problem here?

Rob

 

[RobN]

Thankyou so much for your help. I don't know if we're going to resolve this
and I think we've all probably spent too much time on it. In my reply to
Dave, I've found a workaround which I'm happy to go with before I lose the
rest of my hair!!

However, If you're still inclined to help me get an answer to this problem
I'm willing to give anything a go.....
I've got a Celeron 2.66GHz (1 Processor) with 1Gb RAM
The file was probably created in Vs2000, then upgraded at some point to
XP (is that Vs2003??). Since then I now only run 2007.

Rob

Rob,
I was going to make a suggestion, but Dave made it first - wrapping the
particular section of code in the Disable/Enable events statements. I can
see where the Worksheet_Change would fire, but that code doesn't look like
it
should trigger the _SelectionChange event.

Let me ask this - what type of computer (what CPU) are you using? I do
know
that there are some issues with multi-core CPUs when performing some
operations within VBA, but I can't be more specific than that: I only know
of
one particular situation involving a UDF and a particular VBA function,
and
your code isn't a UDF and it's not using the command I know about. And
that
issue just causes a delay in results, not total misbehavior.

Another question, kind of related to the macro enabled/disabled problem:
You
say you're running this in 2007 and [Compatibility Mode] - what version of
Excel was actually used to create it? I don't have specific ideas at the
moment - just trying to gather info for others that may read all of this
and
maybe ring a bell for them.

Thanks for your efforts Dave. I tried it with the events disabled and
still
no go.

I find it really odd that part of the code within the same procedure
copies
a value successfully to one of the hidden rows, namely, If Range("U9") =
True Then Range("V17") = CloseBal.
Could it be that it doesn't like doing multiple cells particularly a mix
of
hidden and unhidden?

Rob

If you want to avoid whatever event is firing (I don't know if that's a
good
idea, though):

application.enableevents = false
With Sheet4
.Range("V13:V20").Value = .Range("R5:R12").Value
End With
application.enableevents = true

(I don't think it has anything to do with compatibility issues.)

Heck, it won't take long to test this--even if you discard it later!


RobN wrote:

Dave,

Yes, this is a hugely complicated workbook with Worksheet_Change and
Worksheet_SelectionChange on that sheet.

I could post all of the sheet's code here, and also the full code from
the
UserForm which includes the code in question, but there are also
numerous
Modules to which the sheet code refers, and providing all that to
really
nut
out what could cause the problem may be rather tedious.

I suspect I'm just having a glitch with this and that there isn't
actually
an error in the code at all. As you have confirmed, the code works
fine.

I must point out, and maybe this may have a cause, is that I'm running
this
file in [Compatibility Mode] so that others not having 2007 can have
acces
to it. I have tried to save as an XLSM file to test if the problem
still
occurs when running as 2007 version, but even though it saves OK, I
can't
run any macros, even though they are there! It says they are disabled.
However, checking the Macro security, I find it is set to "Enable all
macros".

I'm out of ideas, except that I think I'll just transfer the Range to
an
area that can remain unhidden.

If you have any help regarding the macro disabled problem, that would
also
be appreciated.

Rob

I opened xl2007 (not visual studio!) and tested your code.

It worked fine.

Could you have some sort of event macro running, too?

ps. I hid the rows manually.

RobN wrote:

Dave,

I'm using the dreaded 2007 version, which is giving me grief in
other
areas
as well...GRRRrrrr!! I'm certainly not recommending anyone use this
version
at all.

Rob

I couldn't duplicate the problem in xl2003.

What version of excel are you using? Maybe someone with that
version
could try
to duplicate it.

RobN wrote:

I have the following code as part of other code which is
executed
via
a
UserForm.
With Sheet4
.Range("V13:V20").Value = .Range("R5:R12").Value
End With

When I unhide those rows the code works fine.

Part of the range, namely rows 16 to 23 are hidden. When I
execute
this
code, those cells located within the hidden range do not reflect
the
values
from the R5:R12 range. No error message appears, yet only values
are
copied
into the unhidden cells.
Other parts of the code within this procedure do copy the value
to
the
cells
in the hidden range, eg....
If Range("U9") = True Then Range("V17") = CloseBal

Can anyone see a problem here?

Rob

 

[Dave Peterson]

I use xl2003 and I didn't have any trouble if I hid the rows or changed the
rowheight to 0.

I didn't try it in xl2007, though.