Defining Names for variable ranges Using VB

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

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
--
AP

"ChemistB" <ChemistB.27ngca_1147361705.0766@excelforum-nospam.com> a écrit
dans le message de news:
ChemistB.27ngca_1147361705.0766@excelforum-nospam.com...
>
> 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
>
>
> --
> ChemistB
> ------------------------------------------------------------------------
> ChemistB's Profile:
> http://www.excelforum.com/member.php...o&userid=12741
> View this thread: http://www.excelforum.com/showthread...hreadid=541141
>

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" <ChemistB.27ngca_1147361705.0766@excelforum-nospam.com> wrote in
message news:ChemistB.27ngca_1147361705.0766@excelforum-nospam.com...
>
> 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
>
>
> --
> ChemistB
> ------------------------------------------------------------------------
> ChemistB's Profile:
http://www.excelforum.com/member.php...o&userid=12741
> View this thread: http://www.excelforum.com/showthread...hreadid=541141
>

Thanks. Both of these techniques seem to work!

ChemistB