Hidden Property 1004 Error, Union of Rows

[Neal Zimm]

Hi All,
Am getting the 1004 unable to .. hidden property run time error.
I think I've proved it resulted from a union of rows as
shown in the code below.

I'm gonna use the workaround after cleaning up the code
but the broader question is are there other suspect
properties concerning a range resulting from a union ?

It's a morale buster.

Would appreciate comments on best way to proceed.

Thanks,
Neal Z.



Function PPDtEndRng_GetF(WkTimeWs As Worksheet, _
optional bThisMacHides as Boolean = False, ..more args) as Range

' more code not shown

With WkTimeWs
' PPsRowsHideSM constant is "26:26"
Set RowsRng = .Rows(PPsRowsHideSM)

'more code

' Line below is the 'bad' one
Set RowsRng = Application.Union(.Rows(FromRow & Colon & ToRow), _
RowsRng)

End With

If bThisMacHides Then GoSub HideRows

'more code

Exit Function


HideRows: 'Events, protection, and hide.

Dim bSave As Boolean, DebugRng As Range

Application.EnableEvents = False

With WkTimeWs 'debug code
bSave = .ProtectContents
ToRow = 26
If Not .Rows(FromRow & Colon & ToRow).Hidden Then MsgBox "not hidden"
' Yes
Set DebugRng = .Rows(FromRow & Colon & ToRow) 'equivalent of RowsRng
End With

MsgBox RowsRng.Address & " " & DebugRng.Address 'both $24:$26 no 1004 error

On Error Resume Next
MsgBox DebugRng.Hidden 'False as expected
MsgBox Err, , "DebugRng.Hidden": Err = 0 'OK no error

On Error Resume Next

If RowsRng.Hidden Then '1004 Unable.. prior to putting in On Error
'QED: Can't trust the RowsRng result of the Union re: .Hidden ????
GoTo workaround
Else
If Not WkTimeWs.ProtectContents Then
RowsRng.Hidden = True
Else
Call UNprotectPW(WkTimeWs, PW)
RowsRng.Hidden = True
Call ProtectPW(WkTimeWs, PW)
End If
End If

workaround: ' NO problem here, worked fine.
If Not DebugRng.Hidden Then

If bSave Then Call UNprotectPW(WkTimeWs, PW)

DebugRng.Hidden = True
End If

If bSave Then Call ProtectPW(WkTimeWs, PW)
Application.EnableEvents = True
Return

 

[joel]

from
Set RowsRng = Application.Union(.Rows(FromRow & Colon & ToRow), _
RowsRng)
to
Set RowsRng = Application.Union(.Rows(FromRow & ":" & ToRow), _
RowsRng)

 

[Neal Zimm]

Sorry Joel, I should have told you or modified the code I listed re: your
answer

from
Set RowsRng = Application.Union(.Rows(FromRow & Colon & ToRow), _
RowsRng)
to
Set RowsRng = Application.Union(.Rows(FromRow & ":" & ToRow), _
RowsRng)

Colon is a public constant and is indeed valued at ":"
I have about 10 public constants for punctuation as I have a bad pinky
finger and cannot type the ' or " characters easily.

Please note that my sample code says RowsRng is being 'properly' formed,
it's the .hidden property of it that's the problem.

Please re-read my proof of the problem .
Thanks,
Neal

 

[keiji kounoike]

I can't understand what you are doing in your workaround.
So, I try to modify your pseudo code, deleting all of debug code and on
error code. In my thought, the point is to add Entirerow to RowsRng.

Function PPDtEndRng_GetF(WkTimeWs As Worksheet, _
optional bThisMacHides as Boolean = False, ..more args) as Range

' more code not shown

With WkTimeWs
' PPsRowsHideSM constant is "26:26"
Set RowsRng = .Rows(PPsRowsHideSM)

'more code

' Line below is the 'bad' one
Set RowsRng = Application.Union(.Rows(FromRow & Colon & ToRow), _
RowsRng)

End With

If bThisMacHides Then GoSub HideRows

'more code

Exit Function


HideRows: 'Events, protection, and hide.

Application.EnableEvents = False
If WkTimeWs.ProtectContents Then Call UNprotectPW(WkTimeWs, PW)

RowsRng.EntireRow.Hidden = True '<<==Not "RowsRng.Hidden = True"

If bSave Then Call ProtectPW(WkTimeWs, PW)

Application.EnableEvents = True

Return

Keiji

 

[Neal Zimm]

Hi Keiji

I put the workaround code first in the Sub below to show you what works.
It's followed by the extract from my App Function which does not work.

I've run the code and still get the 1004 error.
My ranges are NOT cell ranges but row ranges, so I don't think
cellrng.entirerow is applicable.

I'm still at a loss. What do you think? I'm running excel 2002.
The worksheet is not protected.

Sub Union_Hidden_Test()
Dim DebugRng As Range
Dim RowsRng As Range
Dim OneRng As Range
Dim TwoRng As Range
Dim FromRow As Long, ToRow As Long

Rows("18:23").Hidden = False 'set up test

'equivalent Workaround code

FromRow = 20: ToRow = 22

Set DebugRng = Rows(FromRow & ":" & ToRow)

MsgBox DebugRng.Address 'got $20:$22 as expected

DebugRng.Hidden = True 'rows hide correctly

If DebugRng.Hidden Then MsgBox "hidden" Else MsgBox "not hidden"
' above displayed hidden


Rows("18:23").Hidden = False 'set up next test

Rows("21:22").Hidden = True
Rows("20:20").Hidden = True

Set OneRng = Rows("21:22")
Set TwoRng = Rows("20:20")

Set RowsRng = Application.Union(OneRng, TwoRng)

MsgBox RowsRng.Address 'got $20:$22 as expected

'line below gets 1004 error, unable to get the hidden property of the range
' class 'Can you explain why ?

If RowsRng.Hidden Then MsgBox "hidden" Else MsgBox "not hidden"
End Sub

 

[keiji kounoike]

Hi Zimm

See the change below and check this would work or not.

Keiji

Hi Keiji

I put the workaround code first in the Sub below to show you what works.
It's followed by the extract from my App Function which does not work.

I've run the code and still get the 1004 error.
My ranges are NOT cell ranges but row ranges, so I don't think
cellrng.entirerow is applicable.

I'm still at a loss. What do you think? I'm running excel 2002.
The worksheet is not protected.

Sub Union_Hidden_Test()
Dim DebugRng As Range
Dim RowsRng As Range
Dim OneRng As Range
Dim TwoRng As Range
Dim FromRow As Long, ToRow As Long

Rows("18:23").Hidden = False 'set up test

'equivalent Workaround code

FromRow = 20: ToRow = 22

Set DebugRng = Rows(FromRow & ":" & ToRow)

MsgBox DebugRng.Address 'got $20:$22 as expected

DebugRng.Hidden = True 'rows hide correctly

If DebugRng.Hidden Then MsgBox "hidden" Else MsgBox "not hidden"
' above displayed hidden


Rows("18:23").Hidden = False 'set up next test

Rows("21:22").Hidden = True
Rows("20:20").Hidden = True

Set OneRng = Rows("21:22")
Set TwoRng = Rows("20:20")

Set RowsRng = Application.Union(OneRng, TwoRng)

MsgBox RowsRng.Address 'got $20:$22 as expected

'line below gets 1004 error, unable to get the hidden property of the range
' class 'Can you explain why ?

If RowsRng.Hidden Then MsgBox "hidden" Else MsgBox "not hidden"

if you change above code to the below, what would happen?

If RowsRng.EntireRow.Hidden Then MsgBox "hidden" Else MsgBox "not hidden"

 

[keiji kounoike]

Hi Neal

One more thing.
As i said in my previous post, if you want to hide rows obtained by
using union, you need to use entirerow property to hide rows.
So,
Set OneRng = Rows("21:22")
Set TwoRng = Rows("20:20")
Set RowsRng = Application.Union(OneRng, TwoRng)
RowsRng.EntireRow.Hidden=true

would hide the unioned rows

Keiji

 

[Neal Zimm]

K - Well, I am embarrassed, .entirerow worked, when u mentioned it the

first time I could not believe that when union'ing rows, that you would
need this property, after all, entire rows are being union'd.

There's still the question of why the (fromrow & ":" & torow) worked. It
must be that in this case the rows are contiguous.

My guess is that Msoft does not consider a union of rows to be rows, but
rather a bunch of cells, hence .entirerow is needed.

Thanks again,
Neal

 

[keiji kounoike]

I may be wrong, but union returns ranges if it seems to look like to be
rows, so you need to use entirerow property. the code below might
explain difference between ranges and rows. For setting a hidden
property to ranges, it seems to need rows but ranges.

Sub test()
On Error Resume Next

'This fails to hide though range is entirerow
Range("$10:$12").Hidden = True
If Err.Number <> 0 Then
MsgBox "Range(""$10:$12"").Hidden=True Failed"
Err.Clear
Else
MsgBox "Range(""$10:$12"").Hidden=True Succeeded"
End If

'This also fails to hide though this select entirerow
Range("$13:$14").Select
Selection.Hidden = True
If Err.Number <> 0 Then
MsgBox "Range(""$13:$14"").Select" & Chr(10) & _
"Selction.Hidden=True Failed"
Err.Clear
Else
MsgBox "Range(""$13:$14"").Select" & Chr(10) & _
"Selction.Hidden=True Succeeded"
End If

'This succeed to hide rows
Rows("$20:$21").Hidden = True
If Err.Number <> 0 Then
MsgBox "Rows(""$20:$21"").Hidden=True Failed"
Err.Clear
Else
MsgBox "Rows(""$20:$21"").Hidden=True Succeeded"
End If

'This fails to hide rows though it seems to be same as above
Rows("$22:$23").Select
Selection.Hidden = True
If Err.Number <> 0 Then
MsgBox "Rows(""$22:$23"").Select" & Chr(10) & _
"Selection.Hidden=True Failed"
Err.Clear
Else
MsgBox "Rows(""$22:$23"").Select" & Chr(10) & _
"Selection.Hidden=True Succeeded"
End If

'but this succeed to hide
Rows("$25:$26").Select
Selection.EntireRow.Hidden = True
If Err.Number <> 0 Then
MsgBox "Rows(""$25:$26"").Select" & Chr(10) & _
"Selection.Hidden=True Failed"
Err.Clear
Else
MsgBox "Rows(""$25:$26"").Select" & Chr(10) & _
"Selection.EntireRow Hidden=True Succeeded"
End If

End Sub

Keiji

 

[Neal Zimm]

Many thanks for your extra time on this Keiji,
I'll run your code to solidfy my knowledge.

In the App I'm building, it's important I have a 'method' I can trust.
You have helped a ton.
Thanks again.