Need some help with Resize. Thanks
 

I have written this procedure to hide a block of rows. All of the statements
work except the ".Resize" statement. If I take it out and replace it with
".Select," the correct rows are selected and hidden.

Dim ws As Worksheet
Dim strPubListRN As String, strQuestListRN As String
Dim namPList As Name, namQlist As Name
Dim rngToHide As Range
Dim numRowsToHide As Long
Set ws = ActiveSheet
Set namPList = Names("PubList")
Set namQlist = Names("QuestList")
Set rngToHide = ws.Range(ws.Range(namQlist),
ws.Range(namPList)).EntireRow
numRowsToHide = rngToHide.Rows.Count - 1
With rngToHide
.Resize(numRowsToHide)
.Hidden = True
End With

But when I reintroduce the ".Resize" statement, I get an error "Invalid use
of the property Resize."

What is wrong?

John Wirt

Need some help with Resize. Thanks
 

With rngToHide
.Resize(numRowsToHide).Entirerow.Hidden = True
End With



Regards,
Tom Ogilvy

John Wirt wrote in message
...
I have written this procedure to hide a block of rows. All of the
statements
work except the ".Resize" statement. If I take it out and replace it with
".Select," the correct rows are selected and hidden.

Dim ws As Worksheet
Dim strPubListRN As String, strQuestListRN As String
Dim namPList As Name, namQlist As Name
Dim rngToHide As Range
Dim numRowsToHide As Long
Set ws = ActiveSheet
Set namPList = Names("PubList")
Set namQlist = Names("QuestList")
Set rngToHide = ws.Range(ws.Range(namQlist),
ws.Range(namPList)).EntireRow
numRowsToHide = rngToHide.Rows.Count - 1
With rngToHide
.Resize(numRowsToHide)
.Hidden = True
End With

But when I reintroduce the ".Resize" statement, I get an error "Invalid
use
of the property Resize."

What is wrong?

John Wirt

Need some help with Resize. Thanks
 

Tom,
Thanks for responding. It works. Can you explain why it works?
Thank you.
John

"Tom Ogilvy" wrote in message
...

With rngToHide
.Resize(numRowsToHide).Entirerow.Hidden = True
End With

Regards,
Tom Ogilvy

John Wirt
I have written this procedure to hide a block of rows. All of the
statements
work except the ".Resize" statement. If I take it out and replace it
with
".Select," the correct rows are selected and hidden.

Dim ws As Worksheet
Dim strPubListRN As String, strQuestListRN As String
Dim namPList As Name, namQlist As Name
Dim rngToHide As Range
Dim numRowsToHide As Long
Set ws = ActiveSheet
Set namPList = Names("PubList")
Set namQlist = Names("QuestList")
Set rngToHide = ws.Range(ws.Range(namQlist),
ws.Range(namPList)).EntireRow
numRowsToHide = rngToHide.Rows.Count - 1
With rngToHide
.Resize(numRowsToHide)
.Hidden = True
End With

But when I reintroduce the ".Resize" statement, I get an error "Invalid
use of the property Resize."

What is wrong?

John Wirt