Intersect - Variable Range - Error

[meledward23]

So I am using intersect to clearcontents, as shown below. I am getting an "Object variable or with block variable not set" and I am stuck.
If I manually enter the resulting Range name (option2x) instead of the variable range name (rngselect), then it works.

Dim rngselect As Range
Dim lrow As Long
Dim rng As Range

Set rngselect = Range("assemselect") 'user selected range result on demo is "OPTION2X"

'show only user selected range
lrow = Sheets("HS Template").Range("ag" & Rows.Count).End(xlUp).Row
Range("ag1:ag" & lrow).EntireRow.Hidden = True
Range("a1:a5").EntireRow.Hidden = False
Range(rngselect).EntireRow.Hidden = False

'clear manual data in range
msgbox rngselect 'results in "option2x"
Application.Intersect(rngselect, Range("c:c")).clearcontents 'if I use application.intersect("Option2x",range("c:c")).clearcontents this works

Application.GoTo ActiveSheet.Range("A1"), True

 

[MARK858]

Are you on the "HS Template" sheet when you run the code?

 

[meledward23]

Are you on the "HS Template" sheet when you run the code?

Yes.

 

[MARK858]

Personally I would be expecting

Range(rngselect).EntireRow.Hidden = False

to error first with a 1004 error

 

[MARK858]

What is the range of Range("assemselect")?

 

[meledward23]

Personally I would be expecting

Range(rngselect).EntireRow.Hidden = False

to error first with a 1004 error

Works fine.

 

[MARK858]

Works fine.

Then surprised... if assemselect is a named range
What was the answer to

What is the range of Range("assemselect")?

 

[meledward23]

Assemselect is a dropdown, where the user selects Option1x, Option2x, etc to option 39x.
Option1x = a range of say row 3:14,
option 2x = a range of say row 21:32

I have also tried running it where
Option1Y = a range of A3:a14
Option2Y = a range of a21:a32

Which worked fine in the top half. But those dont work with the intersect, so I am now running option#x

 

[MARK858]

Option1x = a range of say row 3:14,
option 2x = a range of say row 21:32

If that is the case why do you need to use EntireRow in the below?

Range(rngselect).EntireRow.Hidden = False

Asking because you get an Error 91 when the ranges don't intersect.

What does

MsgBox rngselect.address

give you?

P.S. I can't see any code line highlighted yellow in your image, have you debugged it?

 

[meledward23]

FALSE INFO REMOVED.