Loop Defining Named Ranges

Hi,
I'm trying to define some ranges with a loop but being a newbie at VBA I'm finding it difficult to solve the error in the code.
First, as the data area is uncertain (variation in the number of rows and columns) i look to the end of the entire data area. Then i want to define intervals of 250 rows with a 30 row difference between each of them. Here is my code:

Please Login or Register  to view this content.

The error that I'm getting is that the name of the range is incorrect. Does anyone have a suggestion?
Thanks in advance.

Just to be clear, you want to define several different named ranges, each having 250 rows, and the new named range starting 30 rows after the previous? If that is the case, try:

Please Login or Register  to view this content.

HTH,
Jason

Jason,
Thanks for the suggestion, i have learned somethings with the code you posted and cleaned up some other code.

In regard to my problem that's exactly what i want to do: "different named ranges, each having 250 rows, and the new named range starting 30 rows after the previous".

Unfortunately, although the error has disappeared, i only get two named ranges: TP and TPy. I believe that the

Please Login or Register  to view this content.

part of the code isn't working as i want. I wanted to have named ranges as TP1, TP2, TP3 and so on.
Do you have any idea why? Thank for the help.

Not sure, it worked for me. What are the values of LastRowSh1 and LastColSh1? Can you post up a copy of your workbook (assuming it doesn't have any sensitive data in it)? Or can you post the full code that you are currently using?

jasoncw,
Thanks again.
I have outputted to a cell the values of LastRowSh1 and LastColSh1 and they are the expected ones.

Here is the file, it's just stock prices ordered by date. http://www.sendspace.com/file/4x1i8m

With the code like this

Please Login or Register  to view this content.

i get an error (Translated i think it's "Number of Arguments is Incorrect") so i tried like this

Please Login or Register  to view this content.

but just get two ranges: TP and TPy.

Thanks for any comment.

I have also notice something, i don't know if the fact that it jumps 30 rows every time it defines a range it would never be the exact same number of LastRowSh1 in

Please Login or Register  to view this content.

.

Well, that makes sense now. Thanks for clearing it up.

So you can use my method above (using an underscore before or after "TP"), or the one suggested above, entering an "a" after the reference.

Thanks a lot to both of you. It now running ok and defining all the ranges.

Sorry to bump an old thread but THANK YOU SO MUCH. I spent an hour googling this same issue to no avail and this thread finally solved my issue. To think all I needed to do was add an underscore as to not confuse Excel....