Changing range for a Named Range VBA

[bcn1988]

Hello,

I have a column that will vary in regards to how many records will be in the range. There could be 20 records in the column or there could be 500 records. I need some code that will change the 'RefersTo' to the proper range. So far I have this code:

Sub dk2()

Dim PMfilter As String

PMfilter = Sheets("PM Validation").Range(Range(Cells(2, 6), Cells(2, 6)), Range(Cells(65535, 6), Cells(65535, 6)).End(xlUp)).Address


ActiveWorkbook.Names("PM_Filter").Delete
ActiveWorkbook.Names.Add ("PM_Filter"), RefersTo:=PMfilter
End Sub

This code first will locate the used range that needs to be re-referenced. The code does go all the way through, however, in the named manager of the workbook, PM_Filter has quotes ("") around the range. When I reference the named range in a data validation list, I get an error stating "The list source must be a delimited list, or a reference to single row or column".

I did figure out why I was receiving this error, and it's because of the quotes around the reference of the range. For example, after running the code the "Refers To" column in the named manager shows ="$F$2:$F$31". This is the correct reference, however the quotes are what is giving me the issue. Does anyone know how to get rid of these quotes? Maybe the code I have so far isn't the right code to complete my task. Please help!

[TMS]

You've created three identical posts. This is against the forum rules, primarily because it wastes a lot of people's time.

You have answers in the first two.

Please learn to be patient ... you're likely to get a better response, and not get as many people's backs up.


Regards, TMS

[bcn1988]

Hey Trever,

I am sorry there are multiple posts on here. I do post frequently and understand the rules. There was an issue with ExcelForum and every instance I pressed Post, the webpage would error out. So I actually had no idea I posted multiple times. In fact, I didn't think I even had posted one until I received an e-mail! I will delete all other posts.

Thank you for responding to my issue! I did leave work and have accessed the forum from home, but I will test first thing in the morning.

[TMS]

Fair enough. The forum has been (a lot) flaky tonight. Pain in the backside!

Note that you have at least one other reply on one of the other threads (before you delete it)

Regards, TMS

[arlu1201]

Duplicate Thread.

Thread closed.