Loop through range and define name(s)

[sous2817]

Hello everyone,

I have a report that is in this general format:

Book1
	A	B	C	D
5	Test1
6		Data
7		Data
8		Data
9		Data
10	Test2
11		Data
12		Data
13		Data
14	Test3
15		Data
16		Data
17		DAta
18		Data
19		Data
20		DAta
21		Data
22		Data
23		DAta
24		Data
25	Test4
26		Data
27		Data
28		Data
Sheet1

Can someone show me a way to loop through the used rows and define the range(s) between the "Test"s? So in the above example, there would be 4 named ranges "Test1" would be B6:B9, Test2 would be B11:B13, Test3 would be B15:B24, etc, etc, etc.

Any help would be greatly appreciated.

 

[onlyadrafter]

Hello,

is this working as expected?

Sub NAME_RANGE()
    For MY_ROWS = 5 To Range("B" & Rows.Count).End(xlUp).Row
        If Not IsEmpty(Range("A" & MY_ROWS).Value) Then
            Range(Range("B" & MY_ROWS + 1), Range("B" & MY_ROWS + 1).End(xlDown)).Select
            ActiveWorkbook.Names.Add Name:=Range("A" & MY_ROWS).Value, RefersTo:=Selection
        End If
    Next MY_ROWS
End Sub

 

[sous2817]

Hello onlyadrafter,

Yes that did exactly what I asked, so thank you for that.

There is one more criteria, and I appologize up front for not being clearer.

Book1
	A	B	C	D
5	Project:XXX
6	Data
7	Data
8	Data
9	Data
10	Project:XXY
11	Data
12	Data
13	Data
14	Project:XXZ
15	Data
16	Data
17	Data
18	Data
19	Data
20	Data
21	Data
22	Data
23	Data
24	Data
25	Project:XZX
26	Data
27	Data
28	Data
Sheet1

Is there a way to do the same thing, but all of the data is in one row. For instance, just named the range between the "Project:{whatever} ". The layout will always be the same, just the lines of "data" between "Project:{Whatever} will vary.

So the range between Project:XXX and Project XXY would be one range, XXY and XXZ another range, etc.

The "Project:" stay constant, the XXY, XXX, etc will change based on the project.

Again, any additional help is greatly appreciated.

 

[onlyadrafter]

Hello,

you mean like this?

Sub NAME_RANGE_2()
    Application.ScreenUpdating = False
    MY_LAST_ROW = Range("A" & Rows.Count).End(xlUp).Row
    MY_COUNT = 5
    Do Until MY_COUNT = MY_LAST_ROW
        If Left(Range("A" & MY_COUNT).Value, 7) = "Project" Then
            MY_PROJ = Mid(Range("A" & MY_COUNT).Value, 10, Len(Range("A" & MY_COUNT).Value))
            Range("A" & MY_COUNT).Offset(1, 0).Select
        Else
            Selection.Resize(Selection.Rows.Count + 1, 1).Select
        End If
        MY_COUNT = MY_COUNT + 1
        If Left(Range("A" & MY_COUNT + 1).Value, 7) = "Project" Or MY_COUNT = MY_LAST_ROW Then
            ActiveWorkbook.Names.Add Name:=MY_PROJ, RefersTo:=Selection
        End If
    Loop
    Application.ScreenUpdating = False
End Sub

 

[sous2817]

Yes, that hits the nail right on the head. Absolutely wonderful! I've just added you to my "If you ever find yourself in Raleigh, North Carolina, I'm buying the first round" list.