VBA - Remove text from named range and table

With a lot of help from you guys I have a macro that performs these 2 tasks:

1. Adds text to a named range and then sorts the range alphabetically
2. Adds the same text to a table

What I now need to do is be able to reverse the procedure and be able to select and item from the named range and remove it from the range and table, then resize the range and table size accordingly.

I have attached an example sheet showing the existing function to help explain what I am trying to achieve, hope this makes sense let me know if not!

What's the Faults sheet all about, what do you need that for?

The fault sheet contains the named ranges

Thanks for your response This is exactly what I am looking to achieve there are however a couple of issues.

The first of which was my fault, when I created this example sheet I didn’t recreate the layout and naming of the sheets as they actually need to be, I have attached an amended example that shows how the sheet names are actually named and how they relate to the product names in the list of products. In a nutshell the product names in the list would be like “Product_Name” and the sheet name would be “Product Name” (no underscores). Really sorry I forgot to include this in my original request I would really appreciate if you could assist in tweaking the code so it works correctly. I've been trying for 2 hours now and I cannot get it to work

The second problem I have is that when the fault is removed from the named range in the “Faults” sheet the named range does not get resized so you are left with a blank space at the bottom of the range, is there a way to adjust the range so that this blank space is removed?

Sorry to be a pain I really appreciate your help with this.

Hi there and thanks for your response, sorry for my delay in responding but for some reason I was unsubbed from the thread so didn't get an email notification.

I'm hoping this won't pose too much of a problem but in my original worksheet I do have lists linking to named ranges for products without the _F, probably should have specified this in my example sheet but I didn't think it would be relevant and didn't want to confuse matters with extraneous information.

I will have a play around with this when I get a chance tomorrow and let you know if I have any issues, once again thanks for your help.

All seems to be working perfectly fine now, there was something else I forgot to mention but I managed to tweak the code to sort that myself.

Thanks for your help.

Arrghh, sorry I thought I had solved that problem but it turns out I had not!
In certain instances there may be a product listed that does not have a corresponding PRODUCTNAME_2F table, how can I tell it to not run the last bit of code (below) if the table does not exist?

Please Login or Register  to view this content.

Haha yes sorry that might help

Please Login or Register  to view this content.

Hello Everyone,

This seems to be a thread with members who have lots of knowledge in macros. I was wondering if there was one I can create that will replace one range of cells with another range throughout the entire workbook?

See attached for the situation that I am dealing with.

Thanks!

I inserted the code but it doesn't seem to remove anything from the PRODUCTNAME_2F table now :/

Would it be easier if I post an example sheet showing the problem?

I would post the original sheet I'm working on but it contains company information I don't think I would be allowed to sent it..

Remove Fault Example v2.xlsm

See attached - I have pre selected the product so just click 'Remove Fault' and it should show you the error.

Thanks, no rush whenever you get the chance.

Hi

Yes unfortunately in all instances the fault needs to be removed from the named range on the 'Faults' sheet, and also the PRODUCTNAME_2F table if it exists.

Upon re-reading I think I may have misread what you meant in the first sentence, will have a bash at this tomorrow thanks.

Yes that is exactly what I am looking to achieve, in effect the primary function is to remove the fault from the list on the sheet of faults, then if the product table also exists to remove it from there.

So it sounds as though what you have changed should do the trick, will confirm tomorrow. Thanks again.

Ok.... so I tested out what you sent and that seems to be working fine now but I seem to have spotted another issue.

For some reason when you try to remove a fault that is at the bottom of a table the following line of code throws up an error

Please Login or Register  to view this content.

If you open the attached sheet I have preselected fault that will show the error.

Remove Fault Example v3.xlsm

Hi

I need to know how to define the variable 'lo'

Now it's asking for loRowNum haha sorry

It is row number variable so:

Please Login or Register  to view this content.