Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Named Ranges (Excel 2000)

    Hi everybody

    I was wondering if it is possible to create code that will name ranges automatically. I attach a workbook to show what I have in mind.
    In example 1 there are blue cells and if you put your cursor on any of the blue cells you will see a range name.

    My problem is however that I have several cases where I have to create more than 60 names that begin with the letters at the top of the column followed by the number on the left in the row. That mean the range name will consists of the letters at the top plus the number directly to the left of the particular cell.

    The macro must run as long as there are numbers on the left.

    Is this possible? Any ideas will be appreciated.

    Regards
    Regards
    Kobus

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Named Ranges (Excel 2000)

    You cannot have several ranges with the same names, so you must delete the names in Example1 before trying to assign names in Example2. The following code will do that, you can modify it to suit your needs:

    Sub MakeNames()
    Const lngStartRow = 4 ' row 4
    Const lngStartCol = 6 ' column F
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim lngCol As Long
    Dim lngMaxCol As Long
    lngMaxRow = Cells(65536, lngStartCol).End(xlUp).Row
    lngMaxCol = Cells(lngStartRow, 256).End(xlToLeft).Column
    For lngRow = lngStartRow + 1 To lngMaxRow
    For lngCol = lngStartCol + 1 To lngMaxCol
    Cells(lngRow, lngCol).Name = _
    Cells(lngStartRow, lngCol) & Cells(lngRow, lngStartCol)
    Next lngCol
    Next lngRow
    End Sub

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Named Ranges (Excel 2000)

    Hans

    Thank you very much. I copied the code into a module but the macro do not want to run. I attached the revised workbook. Can you please be so kind as to advice what I did wrong.

    Regards
    Regards
    Kobus

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Named Ranges (Excel 2000)

    In your worksheet, the table begins in row 3, column 5 (column E), so the constants at the beginning of the macro must reflect that:

    Const lngStartRow = 3
    Const lngStartCol = 5

  5. #5
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Named Ranges (Excel 2000)

    Hans

    Thank you very much, works perfect.

    This will definitely save me many hours of work.

    Regards
    Regards
    Kobus

Posting Permissions

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