Rows.Count error in dynamic range naming

Please help.

The attached workbook is designed to be used by my receptionists as and when required.

My problem is getting the dynamic ranges 'RCL' & 'LCL' to be accurate.

At the moment I have tried one method to name 'RCL' but that only gives me one line, and another method for 'LCL' which gives me all my options but then includes thousands of blank lines.

I have included the code below to save anyone trawling through everything.
This is my first project proper with VBA so am learning as I go along.

Please Login or Register  to view this content.

That is some of the code from the userform I am using.

Upon clicking on 'Search' data is advance filtered and copied to the worksheet 'RCL'.
It then sets the dynamic range and calls it 'RCL' (I now see that I should rename one of these to make it clearer.)

At least that was the idea. I appreciate you looking at it but is there any chance you could expand a bit more on what the solution to my problem seems to be.

OK, so the piece of code I used before now seems to be working on my home PC.

Please Login or Register  to view this content.

The only problem being that if they choose an option within the program that gives a fairly short list, if they then change it to an option that would give more results, it doesn't expand the list.

I was trying to write the Rows.Count into the code at a point after the filtered results had been copied through so that the number of rows would be counted fresh each time. This doesn't seem to be happening and I have no idea on how to sort it.

Help, please......

No ideas?

Am out of my depth on this one.

Thank-you for tidying up my code, it looks a lot neater your way.

Please Login or Register  to view this content.

This unfortunately is only reading the first row even though there is always more than 1 row copied across. Any ideas on that?

I have gone back to using the following formula in the named ranges.

Please Login or Register  to view this content.

This has stopped the problem of only get one entry returned.
It also returns the exact number of rows on the first 'search' but on subsequent searches, is returning the same number of rows as the first search even if the new search should return more or less options.

Is the dynamic range not meant to automatically adjust to any changes in the sheet?
Is the problem because I am copy/pasting onto the sheet?

Is there a way to use the above code within the VBA code attached to the 'Search' button to make it re-evaluate how many rows are required?

I don't know why your solution is working for you and not for me but I have checked it on various PC's and they are all the same. Have you closed the userform and re-run?

OK, I have deleted out from the RowSource field any references to any ranges.

Where should I be putting your code romperstomper? I am also presuming that I substitute "blah" for the name of the sheet that the range is found, am I right?

Thanks for all the help from everyone so far.

The error is:

Run-time error '381'
Could not set the list property. Invalid property array index.

I have marked in the code box where the de-bugger takes me.

The range RCL is a dynamic list on the worksheet 'RCL' and changes as the search parameters are changed. It could be anything from one cell to 40 cells.

This is really weird.

I substituted in your new code and thought 'Hallelujah!', it worked!!
Then I changed one of the search parameters and searched again.
The list imported was reduced to one item only for each box. There should have been multiple for both.

I have re-attached the workbook so you can see what happens now.

It is only the CL Criteria section that seems to be causing problems.
I am selecting;
'Soft' for both R&L
'Monthly' for R&L
'Spherical' for R, 'Toric' for L
'Single Vision' for R&L

This first search brings me my lists in for both the lens selection boxes.
If I now change the 'Toric' to 'Spherical' it no longer works properly.

Aaaaarrrrggghhhh!