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

 

[meledward23]

I am not cemented on this layout. If you can think of a better way to achieve this. I just have 40 ranges, each with over 50 columns. I want to be able to filter down to the one the user selects. and make sure all user enter data is cleared, but it leaves the standardized text in certain columns.
This is the only solution I can think of.

 

[meledward23]

I lied... $K$2 - the address of assemselect

 

[MARK858]

Well K2 doesn't intersect with Column C which is why you get the error, you need

Application.Intersect(rngselect.EntireRow, Range("c:c")).ClearContents

which will clear C2

 

[meledward23]

Right, so...

this unhides the Range, not $k$2, but the range Identified/named by K2 (Option2x = rows 13:21)
Not sure why it works here, but not there.


Oh well, I can clear visible cells if they are user ranges instead of headers so,


*******
lrow = Sheets("HS Template").Range("ag" & Rows.Count).End(xlUp).Row

On Error Resume Next
For Each rngrow In Range("c5:c" & lrow).SpecialCells(xlCellTypeVisible)
If Range("ag") = "ASSEM" Then
rngrow.Cells(3).clearcontents
******

And it works.
Thank you for your time , you helped me solve this.

 

[MARK858]

this unhides the Range, not $k$2, but the range Identified/named by K2 (Option2x = rows 13:21)

So what happens with

Application.Intersect(Range(rngselect).EntireRow, Range("c:c")).ClearContents

 

[meledward23]

That also works.
Thanks!

 

[MARK858]

You're welcome