Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Hidden Property 1004 Error, Union of Rows
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 345
Default Hidden Property 1004 Error, Union of Rows
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 .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
--
Neal Z
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Hidden Property 1004 Error, Union of Rows
from
Set RowsRng = Application.Union(.Rows(FromRow & Colon & ToRow), _
RowsRng)
to
Set RowsRng = Application.Union(.Rows(FromRow & ":" & ToRow), _
RowsRng)


"Neal Zimm" wrote:

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 .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
--
Neal Z
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 345
Default Hidden Property 1004 Error, Union of Rows
Sorry Joel, I should have told you or modified the code I listed 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



--

Neal Z


"joel" wrote:

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


"Neal Zimm" wrote:

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 .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
--
Neal Z
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 199
Default Hidden Property 1004 Error, Union of Rows
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 wrote:
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 .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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 345
Default Hidden Property 1004 Error, Union of Rows
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
--
Neal Z


"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

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 wrote:
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 .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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 199
Default Hidden Property 1004 Error, Union of Rows
Hi Zimm

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

Keiji

Neal Zimm wrote:
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"

End Sub
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 199
Default Hidden Property 1004 Error, Union of Rows
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 wrote:
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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 345
Default Hidden Property 1004 Error, Union of Rows
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



--
Neal Z


"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

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 wrote:
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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 199
Default Hidden Property 1004 Error, Union of Rows
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 wrote:
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



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 345
Default Hidden Property 1004 Error, Union of Rows
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.

--
Neal Z


"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

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 wrote:
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




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Rows hidden by Autofilter vs hidden by changing the Hidden property LEO@KCC Excel Programming 4 September 11th 07 10:14 AM
Hiding Rows Error - "Runtime Error 1004 - Unable to set the hidden property of the range class" Punsterr Excel Programming 2 April 9th 07 05:32 PM
1004 - Unable to set the hidden property of the range class Tim Whitley Excel Programming 3 February 20th 06 09:50 PM
Run Time Error 1004 Unable to set hidden property Lester Lee Excel Programming 3 July 22nd 04 03:31 AM


All times are GMT +1. The time now is 01:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement