Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Range Name

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I import data into a sheet with Code, and Name the range with Code, how can I get the Name of the Range to change with the data? For instance if I import data into Range("A1:C100") and name it New, then import Data again and it takes up range("A1:C200"), how can I "New" to associate with the new range? Thanks for your help. -asa
    Excel 2007

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-08 14:40, Aaron wrote:
    If I import data into a sheet with Code, and Name the range with Code, how can I get the Name of the Range to change with the data? For instance if I import data into Range("A1:C100") and name it New, then import Data again and it takes up range("A1:C200"), how can I "New" to associate with the new range? Thanks for your help. -asa
    Aaron,

    You need a dynamic name range. So, which column is of numeric data type (that's, has numbers, dates, hours)?

    Aladin

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The first 3 columns are text, the next 12 are numeric. Is that what you needed?
    Excel 2007

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why not "import" it using Get External Data? It will automatically name and extend the range as needed.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't know how to do the External data importing. Here is my code however:

    Sub ImportTSOData()
    Sheets("MFG_DIRECT").Select
    Range("BY2").Select
    myTSOFile = Application.GetOpenFilename("Text Files,*.*")
    Workbooks.OpenText FileName:=myTSOFile, Origin:=xlWindows, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
    Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array( _
    3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1))
    Selection.EntireRow.Delete
    Selection.End(xlDown).Select
    Selection.EntireRow.Delete
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Copy
    ActiveWindow.ActivateNext
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.CurrentRegion.Select

    ' ActiveWorkbook.Names.Add Name:="DDATA", RefersToR1C1:= _
    ' "=MFG_DIRECT!R2C77:R3485C85"
    Range("A1").Select
    Sheets("PRINT").Select
    ActiveWindow.ActivatePrevious
    ActiveWindow.Close SaveChanges:=False
    End Sub
    Excel 2007

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-08 14:44, Aaron wrote:
    The first 3 columns are text, the next 12 are numeric. Is that what you needed?
    I was thinking that your data area was changing with every import and that you needed a name that takes the changes into account. If this is correct, may I ask whether numeric columns also change along with the text columns?

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The first three colums will always be text. I may have more columns, and more rows depending on the user. In my code above I would have to change the edited comments everytime I updated the Workbook. I think a Dynamic range is the way to go, I just don't know.
    Excel 2007

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 14:47, Aaron wrote:
    I don't know how to do the External data importing. Here is my code however...
    No code is required... See the Data | Get External Data menu command.

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-08 14:50, Aaron wrote:
    The first three colums will always be text. I may have more columns, and more rows depending on the user. In my code above I would have to change the edited comments everytime I updated the Workbook. I think a Dynamic range is the way to go, I just don't know.
    Aaron,

    Investigate first this get External Data thing with Mark. If that operation already associated with a name (I trust it does), then you won't need to define a name yourself. Otherwise, we can always define a dynamic name range for the data area of interest.

    Aladin

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No code is required... See the Data | Get External Data menu command.

    I have never used that functionality before, any hints or tips?
    [/quote]
    Excel 2007

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
  •