Error setting range to hidden in Excel 2003, not in 2000
Hi All,
I'm having a problem where I recieve the following error when trying to set the hidden property for a named range in Excel via VBA:
Error 1004 Unable to set the hidden property of the range class
However, this is only occuring on machines with Excel 2003, not on machines with Excel 2000.
Code example:
Range("nmRevisions").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Else: Selection.EntireRow.Hidden = True
End If
The named range is valid. Any thoughts?
Thanks,
DonC
[638 byte] By [
DonC] at [2007-12-23]
Don,
I got similar messages with my application that "Conditionally" hids rows. It worked fine in Excel 2002 and started running into problems with Excel 2003 and later. Other symptoms included a flickering display, endless loops and "Out of Stack Space" messages.
I traced it down to a change in how Visual Basic for Applications handles the "hidden" properties code.
In the past, setting an entire row to hidden would do just that. It didn't care what was in the row that was being hidden (or unhidden).
In Excel 2003 and later, when a row is hidden or unhidden, the code looks to see if there is any code that depends on things that are in the row that is being hidden or unhidden. If so, the "dependent" code is executed, much like a subroutine, and once it is done, the next line after the "EntireRow.Hidden = True/False" is executed.
The same thing happens when you replace "EntireRow.Hidden = True" with "RowHeight = 0".
As a work around, I replaced "EntireRow.Hidden = True", with "RowHeight = 0.7" and "EntireRow.Hidden = False" with "RowHeight = 15.75".
