Writing Excel VBA script to handle range names

[AliR]

I am importing a file into excel, and want to assign a range name to the import. The file changes in the number of columns and/or row that are imported. I am trying to write a macro that will change the the number of rows and columns of that named range each time the file is imported.

Can anyone help?

 

[Zathras]

Are you starting with a new sheet for every import?

If not, how are you handling the case where the new import has fewer rows and/or columns than the data you already have?

(If you are starting with a new sheet, you should be able to use the UsedRange property of the sheet.)

 

[Gavona]

Or, use CurrentRegion:
select a cell within the imported range (perhaps use find to locate a known fieldname) then

Selection.CurrentRegion.name = "myname"

NB will not work if your import range includes blank rows or columns

Also if you are importing into an existing sheet consider dynamic rangenames

By the way, this post should really be in the VBA forum so, without VBA:
Edit,Goto,Special,Current region
(this is really innaccesible for such a useful function so I add it to my toolbar (along with VisibleCellsOnly)

 

[DaleWatson123321]

AliR,

With the following routine, you don't have to be concerned whether your imported data has blank rows or blank columns.

This will assign the range name "data" to the imported data. You can of course choose whatever name you prefer to use instead of "data".

Sub Set_Data()
Application.Goto Reference:="R1C1"
FirstCell = ActiveCell.Address
LastRow = [A65536].End(xlUp).Address
LastColm = [IV1].End(xlToLeft).Address
LastCell = Range(LastRow, LastColm).Address
datalist = FirstCell & ":" & LastCell
Range(datalist).Name = "data"
End Sub

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Loomah]

Gavona
just for info - if you don't have your custom toolbar you can use the key combination CTRL+* to select current region ALT+; for VisibleCellsOnly...

This has got me thinking of something

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Gavona]

Great tip Loomah, I've given you a star for your other post.

Dale, if I've understood your code correctly it assumes that:
* the first row of the import is in row 1
* the first row contains data (or labels/headings) for every column that is used in the database
* similarly assumes that column A will contain data in the last record.


The solution I currently adopt is as follows. I would really value your comments:

'make sure I select the top left cell in my import
'some of my imports seems to start with a blank row or two
'but the first row is fieldnames/columnlabels
Cells.Find(What:="myfirstdatacolumnheading", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False ).Activate

'select everything below and to the right of this cell
'(often this "overselects&quot
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

'sort to get blank rows at bottom of sheet
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

'name the range
Selection.CurrentRegion.Name = "alldata"