Defining Names for variable ranges Using VB

[ChemistB]

Excel 2000
I am importing data, which consists of several “tables” separated by
blank rows. The size of the tables varies so I need to name the tables
in the macro, not before hand. I have the code I used below. First, I
go to the beginning of the data (Cell B6), then select the first set of
data, try to name it (Table1) and then select the second set of data
and try to name that. I think the error is definitely in the line
where I name the tables but I am not sure how that should be worded.
I tried doing it manually using “Record Macro” but that gave me
specific ranges, which did not alter with newly imported data. Anyone
have any ideas? Thanks in advance.

'Name Table1 and Table2
'
Range("B6").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="TABLE1", RefersToR1C1:= _
Active.Selection
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="TABLE2", RefersToR1C1:= _
Active.Selection

ChemistB

 

[Ardus Petus]

Sub test()

'Name Table1 and Table2
'
Range("B6").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="TABLE1", RefersToR1C1:= _
Selection
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="TABLE2", RefersToR1C1:= _
Selection
End Sub


HTH

 

[Bob Phillips]

Try this

Dim rng As Range
Dim iRows As Long, iLastRow As Long
Dim iCols As Long, iLastCol As Long
Dim iVeryLastRow As Long
Dim iTable As Long

iVeryLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set rng = Range("B6")
iTable = 1

Do
iLastRow = rng.End(xlDown).Row
iRows = iLastRow - rng.Row + 1
Set rng = rng.Resize(iRows)
iLastCol = Cells(rng.Rows.Count + rng.Row - 1,
"B").End(xlToRight).Column
iCols = iLastCol - rng.Column + 1
Set rng = rng.Resize(, iCols)
rng.Name = "TABLE" & iTable
iTable = iTable + 1
Set rng = Cells(iLastRow + 2, "B")
Loop Until rng.Row > iVeryLastRow


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[ChemistB]

Thanks. Both of these techniques seem to work!

ChemistB