Create multiple ranges based on an index of values that are also used as range names

Hello,

I am pretty new to VBA (my third mini-VBA code project ever...pretty excited, actually and this is by far the most complex thing I tried to tackle) I am running into issue with execution of my code and I would like to solicit some suggestions.

Objective: to create multiple named ranges based on index data point pertinent to that specific data set. Then set that index data point as the name of that specific range.

I manually hardcoded an index in column A which contains mostly blank cells and a few data points which I would like to use as names for a range of values to the right of the index. Here is the approach I have taken:

1. loop over the index
2. keep looping if blank
3. end the loop if come to a value "end"
4. if the cell is neither of those then take cell value and use it as a name for a range that should span 7 columns to the left and 40 rows down

Here is the code I have so far, it doesnt execute and I am just too "green" to pick out the issue. Went at it for a few hours now trying a few different things, but no success. Could some one please offer some tips or suggestions?

I am also attaching a sample workbook I am working on...I need this because eventually I will have 30-40 data tables (which might vary in length of rows, but I'll figure that one out later) which I will need to create named ranges for.

Thank you for your time and insights

Code:

Please Login or Register  to view this content.

Thank you for the help. This works great!

Is there a way to make the range select more dynamic? I will have ranges that will span more than 7 cells down and vary in length. Can the formula identify those outliers and create a custom range length for each?

Thank you for your help. This is a huge time saver!

Example.xlsmExample.xlsm

Hello, here is an example workbook with the data.

I manually created an index to assign names to each data set. Now I need to figure out how to keep the range selection dynamic, so when I get a different length data set, the code only encompases that range in the creation process.

I wanted to initially build a loop to test each value within the data set and return the first blank cell and use that as a reference, but I feel like there is a more efficient way to achieve my purpose.

Thank you for your help so far, any suggestions are highly appreciated.

Hey,

Having no rows within the data would work. I can manually delete them or come up with some code for it later (such as if only one row is blank that delete it, if more than one leave it the same).

Here is the example.xls file without the rows in between for the reference.

Thank you very much for your time.Example.xlsx

.CurrentRegion works great. Thank you for all the help!