Loop through range and define name(s)

sous2817

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
Hello everyone,

I have a report that is in this general format:
Book1
ABCD
5Test1
6Data
7Data
8Data
9Data
10Test2
11Data
12Data
13Data
14Test3
15Data
16Data
17DAta
18Data
19Data
20DAta
21Data
22Data
23DAta
24Data
25Test4
26Data
27Data
28Data
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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

is this working as expected?

Code:
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

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
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
ABCD
5Project:XXX
6Data
7Data
8Data
9Data
10Project:XXY
11Data
12Data
13Data
14Project:XXZ
15Data
16Data
17Data
18Data
19Data
20Data
21Data
22Data
23Data
24Data
25Project:XZX
26Data
27Data
28Data
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.
 
Last edited:

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

you mean like this?

Code:
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

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
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.(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top