Name ranges, but stop at blanks or error cells

[chrisellis250]

Hi all,

On the attached I have some tables. They can contain up to 20 values in the 2nd column, i.e. C4:C23 in the first table.

Once this data is produced, I have a code to name the ranges, which works when a table is the only table in a single sheet.

If I run the code below, it selected everything B2 downwards and gives it a name, which selected the two tables on the left.

Please Login or Register  to view this content.

Ultimately, I am looking to amend this code so that it does not select any blank columns, or where there are any errors in the cells - so for example

- with the first table it would select B2:G13.
- with the second table it would select I3:P23
- the third table (bottom left) it would select B25:G24
- the fourth table (bottom right) it would select I26:P46

Anyone have any ideas how to go about this?

Cheers,

Chris

[sintek]

I'm surprised that you still get assistance...
So many of your threads are unanswered...
Here
Here
Here
Here
Just to list a few...

[chrisellis250]

Hi Sintek,

Many thanks for your response.

Re those threads they still remain live while I carry out testing, as I work with complicated and sensitive data.

I apologise for any inconvenience I have caused.

Kind Regards,

Chris

[TMS]

Re those threads they still remain live while I carry out testing, as I work with complicated and sensitive data.

I suspect that, had you shared that information in each thread, the contributors may feel more understanding.

Reality check: a couple of the threads highlighted are over a month old.

[sintek]

Thank you for taking care of your unanswered threads...

On the attached I have some tables

Your sample file contains no Tables and does not match your explanation...
Upload an accurate representation of your file in explain in detail what it is you would want this code to do...

[chrisellis250]

Apologies, I've uploaded the wrong file

Please see the attached.

[sintek]

Does not match this...

- with the first table it would select B2:G13.
- with the second table it would select I3:P23
- the third table (bottom left) it would select B25:G24
- the fourth table (bottom right) it would select I26:P46

As per post 5...

Upload an accurate representation of your file in explain in detail what it is you would want this code to do...

And why oh why would you merge cells!!! It causes havoc with VBA

[TMS]

Can't you just use:

Please Login or Register  to view this content.

[chrisellis250]

Hi Sintek.

So atm when I run the code shown in my first post, it would select B2:G46 - selecting the two tables on the left-hand side, and naming them as one range.

I am looking at how I could amend the code, to only select B2:G23, or ideally B2:G13 as this range is the range in this table with data.

[sintek]

Get rid of those merged cells...

Please Login or Register  to view this content.

[chrisellis250]

Thanks Sintek - the used range in those tables would be name "TopContacts1" "Top Contacts2" and so on.

[sintek]

Here you go...

Please Login or Register  to view this content.

Thanks for rep +

[chrisellis250]

Fantastic sintek! That is brilliant.

[TMS]

Thanks for the rep.

[chrisellis250]

Thanks TMS, I've used IFERROR on my ranking formula to tidy it up a bit, so it looks better! Much appreciated.