Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Select Macro (Excel 2002)

    I need a macro that selects a range of cells and gives that range a name. The range always starts in a cell selected by the user and extends down a single column to the end of data in that column. The problem is that the number of rows containing data in that column is not fixed and thus the column length changes from one time to another.

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Macro (Excel 2002)

    Chuck,

    Does the range include any other columns, or is it the one column only?
    What range name is to be used? It is always the same range name?

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select Macro (Excel 2002)

    Something like this perhaps?

    <pre>Sub NameRange()
    Dim sName As String
    Dim iCol As Integer
    sName = InputBox("Name the range")
    iCol = Selection.Cells(1, 1).Column
    Range(Selection.Cells(1, 1), _
    Cells(65536, iCol).End(xlUp)).Name = sName
    End Sub</pre>


    Steve

  4. #4
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Macro (Excel 2002)

    Hi Steve,

    Thanks a bunch. Your code works just fine if I first select a column, then run the macro and enter the name that I want to give to the range.

    A desirable modification would be to have the macro assign a fixed name to the range. This way the user doesn't have to know how to perform this function, and that's most desireable in this case. All he would have to do is select the column he wants and run the macro to assign the fixed range name to it. I actually use the range name to import the column of data into an Access table for further processing.

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

    Re: Select Macro (Excel 2002)

    Chuck,

    You can simply replace the line

    sName = InputBox("Name the range")

    by

    sName = "ImportRange"

    where"you can replace "ImportRange" by whatever fixed name you would like to use.

  6. #6
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Macro (Excel 2002)

    Thanks Hans, that will do it.

Posting Permissions

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