Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Automatic Range Naming

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    any ideas, willing to try almost anything. thanks

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •