Range Name

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
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 Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Upvote 0
The first 3 columns are text, the next 12 are numeric. Is that what you needed?
 
Upvote 0
Why not "import" it using Get External Data? It will automatically name and extend the range as needed.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
No code is required... See the Data | Get External Data menu command.

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

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top