If named range doesnt exist next k

[jordan2322]

Hi guys,

I'm using the following piece of code:

Please Login or Register  to view this content.

Which simply uses a counter j to toggle through the used range on worksheet Strg, The values that are contained in Range("D" & j) are named range names.. i.e. the range itself exists on a different sheet but the names are listed one below the other. I am using this to copy the actual range and paste it onto Sheets("Temp").

The problem I am having is that sometimes the named range doesnt exist although the name is still included in the list on worksheet(Strg). and so when the macro runs if it doesnt find the named range from D & k then it repastes the last copied named range. How do I modify this in such a way that When a named range doesnt exist it goes next k without pasting?

regards

[rscsmith]

Hi Jordan

Firstly, you don't need to go to each location to do the copy. You can just reference it. This is something that took me some time to realise when I was learning VBA, since the Excel macro recorder always does exactly what you tell it (i.e. Activate, Select, Selection., etc.) It came as a real eye opener when I realised! Makes macros run a lot quicker, and you don't move away from where the user is working.

I have added a Function (you'll need to paste it outside your Sub) to test whether the range exists before you use it.

Please Login or Register  to view this content.

Try this code instead. It assumes you want the pasted list in column A of Temp.:

Please Login or Register  to view this content.

I haven't been able to test the code without your spreadsheet and the rest of your macro. Let me know if it doesn't work.

An alternate code for cycling through the used range on Strg would be would be to cycle through the cells, rather than cycling through the row numbers:

Please Login or Register  to view this content.

Let me know if any of this doesn't work, or if you'd like some explanation of how it does.

Cheers, Rob.

[AlphaFrog]

Please Login or Register  to view this content.