Range Name

[Aaron]

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

[Aladin Akyurek]

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

[Aaron]

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

[Mark W.]

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

[Aaron]

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

[Aladin Akyurek]

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?

[Aaron]

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.

[Mark W.]

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.

[Aladin Akyurek]

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

[Aaron]

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

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