Automatic Range Naming

phiore

New Member
Joined
May 1, 2002
Messages
11
I have a data file, consisting of departments with an indeterminate number of rows in each department. the upper left of the range is e7. i have a column of range names, starting at row7 column 1. the number of departments and range names are the same quantity.
i want to start at e7, determine the size of the range, and name the range the name in row 7 col 1, move down to the next department, and name it the second name in the list i.e.row8 col1, and so on. i want to keep going til the list of names returns a blank. appreciate any help i can get.
This message was edited by phiore on 2002-05-02 09:48
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
I'm not clear with what you're asking, but, you can use:

Range("E7").Name = "Something"

you can determine the last row with data in Column E with

LastRowInE = Range("E65536").End(xlUp).Row

Hope this helps
 
Upvote 0
i can determine the size of each range. i need help with a"do till isempty", taking the names in the designated column and applying them to the range size i have already determined. thanks
 
Upvote 0
I am not following what you are asking either.

maybe try this:

range("E7").select
do until activecell = vbnullstring
'use code here to do what you are trying to do
activecell.offsett(1,0).select

loop

this will go through each row until it reaches an empty row
 
Upvote 0
i'll try to explain. i have no problem writing a macro that will determine how big each range should be. however after i have determined the coordinates of each range, i want the macro to go to another location, take the name in that list, and range name the range that i have already highlighted. thanks
 
Upvote 0
or you could try:

range("E7").select

to determine last unempty row you could do the opposite of what Juan suggested,depending on how much data you have, and try:

selection.end(xldown).select

this will select the last row in the current column that is not empty(given there are no empty cells separating your dat.

Regards,
 
Upvote 0
to help determine your populated ranges try some of these:

selection.end(xltoright).select
selection.end(xlup).select
selection.end(xldown).select
selection.end(xltoleft).select

or

range(activecell,selection.end(xltoright)).select
 
Upvote 0
I HAVE NO PROBLEM IN DETERMINING THE COORDINATES OF EACH RANGE. I NEED HELP IN WRITING A "LOOP" STATEMENT, THAT WILL TAKE A "NAME" THAT EXISTS IN DIFFERENT COLUMN, APPLY IT TO THE RANGE I HAVE DETERMINED, AND RANGE NAME IT AUTOMATICALLY. THEN, DETERMINE THE SECOND RANGE, GET THE "NAME", AND RANGE NAME THAT RANGE. KEEP DOING THIS TILL IVE RUN OUT OF NAMES IN THE COLUMN OF NAMES. THANKS
 
Upvote 0
The "THANKS" at the end of your last post seemed to be oxymoronic at best...

Anyway, here is some code I wrote for someone, maybe you, which searches down column c for a name, places that name in column a, names the range from columns C:S corresponding to the name in c...
Maybe, if your more patient with yourself than you were with Waderw, who seemed to be trying to help you, this code will help you.
THANKS
Tom<pre>



Private Sub cmdGetRange_Click()
Dim LastRow, c, n
Dim TrackNames() As Long
Dim ArrayCnt As Integer
LastRow = Range("D1:D" & Range("D65536").End(xlUp).Row).Rows.Count + 1
ArrayCnt = 1
Columns(1).ClearContents
'deletes all names in workbook
For Each n In ActiveWorkbook.Names
n.Delete
Next
For Each c In Range("C12:C" & LastRow)
If c<> "" Then
ReDim Preserve TrackNames(1 To ArrayCnt)
TrackNames(ArrayCnt) = c.Row
ArrayCnt = ArrayCnt + 1
End If
Next
ReDim Preserve TrackNames(1 To UBound(TrackNames) + 1)
TrackNames(UBound(TrackNames)) = LastRow
For ArrayCnt = 1 To UBound(TrackNames) - 1
Range("A" & ArrayCnt + 11) = _
Range("C" & TrackNames(ArrayCnt))
ActiveWorkbook.Names.Add Name:=Range("A" & ArrayCnt + 11), _
RefersToR1C1:="=Sheet1!" & "R" & TrackNames(ArrayCnt) & _
"C" & 3 & ":" & "R" & TrackNames((ArrayCnt) + 1) - 1 & _
"C" & 19
Next
End Sub</pre>
This message was edited by TsTom on 2002-05-03 18:48
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top