Automatic Range Naming

[phiore]

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 ]

[Juan Pablo González]

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

[phiore]

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

[phiore]

any ideas, willing to try almost anything. thanks

[waderw]

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

[phiore]

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

[waderw]

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,

[waderw]

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

[phiore]

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

[Tom Schreiner]

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

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

[ This Message was edited by: TsTom on 2002-05-03 18:48 ]