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


--
ChemistB
------------------------------------------------------------------------
ChemistB's Profile: http://www.excelforum.com/member.php...o&userid=12741
View this thread: http://www.excelforum.com/showthread...hreadid=541141

Defining Names for variable ranges Using VB
 

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" a écrit
dans le message de news:
...

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

Defining Names for variable ranges Using VB
 

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" wrote in
message ...

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

Defining Names for variable ranges Using VB
 

Thanks. Both of these techniques seem to work!

ChemistB


--
ChemistB
------------------------------------------------------------------------
ChemistB's Profile: http://www.excelforum.com/member.php...o&userid=12741
View this thread: http://www.excelforum.com/showthread...hreadid=541141