I would suggest creating a dynamic named range for that variable data.
Something like

=OFFSET($A$1,,,COUNT($A:$A),1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"VexedFist" wrote in message
oups.com...
Help I have a Macro that opens a Text file downloaded from a Telephone
switch. The file is formatted and then I use various VLOOKUP commands
referencing other worksheets to complete the formatting. The Problem I
have is Two-Fold;

First: the File length is variable any where between 0 rows (empty,
never seen it happen, but) and over 30000 rows since the data is
dispersed;

Second: The VLOOKUP commands need to coorespond to the file length.

Here is what I have so far. I don't know if it would be simpler to
have the referenced workshhets within the same workbook, in the end
they all will be.

Any and all suggestions would be appreciated.


Sub DataSheet()
'
' DataSheet Macro

'
ChDir "C:\Temp Data Files\Reconfigured Data"
Workbooks.OpenText Filename:= _
"C:\Temp Data Files\Reconfigured Data\DataSheet.txt",
Origin:=437, 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, 2), Array(5, 2), Array(6, 2), Array(7, 2),
Array(8, 2), Array(9, 2), Array(10 _
, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2),
Array(15, 2), Array(16, 2), _
Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2),
Array(21, 2), Array(22, 2), Array( _
23, 2), Array(24, 2), Array(25, 2), Array(26, 2), Array(27, 2),
Array(28, 2), Array(29, 2), _
Array(30, 2), Array(31, 2), Array(32, 2), Array(33, 2),
Array(34, 2), Array(35, 2), Array( _
36, 2), Array(37, 2), Array(38, 2), Array(39, 2), Array(40, 2),
Array(41, 2), Array(42, 2), _
Array(43, 2), Array(44, 2), Array(45, 2), Array(46, 2),
Array(47, 2), Array(48, 2), Array( _
49, 2), Array(50, 2), Array(51, 2), Array(52, 2), Array(53, 2),
Array(54, 2), Array(55, 2), _
Array(56, 2), Array(57, 2), Array(58, 2), Array(59, 2),
Array(60, 2), Array(61, 2)), _
TrailingMinusNumbers:=True
Columns("A:BZ").Select
Selection.ColumnWidth = 0.5
Columns("A:BZ").EntireColumn.AutoFit
Columns("A:BZ").Select
Selection.Sort Key1:=Range("E1"), Order1:=xlDescending,
Key2:=Range("D1") _
, Order2:=xlAscending, Key3:=Range("A1"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortNormal,
DataOption3 _
:=xlSortTextAsNumbers
Range("A1").Select
Selection.EntireRow.Insert
Windows("Data Phones.xls").Activate
ActiveWindow.WindowState = xlNormal
Range("A1").Select
Windows("DataSheet.txt").Activate
Range("B2").Select

Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert

Range("B2:M2").NumberFormat = "General"
Range("B2").FormulaR1C1 =
"=IF(RC[-1]"""",VLOOKUP(RC[-1],'[Capacity
Report.xls]Names'!C1:C3,2,FALSE),"""")"
Range("C2").FormulaR1C1 =
"=IF(RC[-2]"""",VLOOKUP(RC[-2],'[Capacity
Report.xls]Names'!C1:C3,3,FALSE),"""")"
Range("D2").FormulaR1C1 = "=IF(RC1"""",VLOOKUP(RC1,'[Data
Phones.xls]Hp4k Optidat'!C1:C10,2,FALSE),"""")"
Range("E2").FormulaR1C1 = "=IF(RC1"""",VLOOKUP(RC1,'[Data
Phones.xls]Hp4k Optidat'!C1:C10,5,FALSE),"""")"
Range("F2").FormulaR1C1 = "=IF(RC1"""",VLOOKUP(RC1,'[Data
Phones.xls]Hp4k Optidat'!C1:C10,6,FALSE),"""")"
Range("G2").FormulaR1C1 = "=IF(RC1"""",VLOOKUP(RC1,'[Data
Phones.xls]Hp4k Optidat'!C1:C10,7,FALSE),"""")"
Range("H2").FormulaR1C1 = "='MACRO Data
Phones.xls'!ExtractElement(RC16,1,""-"")"
Range("I2").FormulaR1C1 = "='MACRO Data
Phones.xls'!ExtractElement(RC16,2,""-"")"
Range("J2").FormulaR1C1 = "='MACRO Data
Phones.xls'!ExtractElement(RC16,3,""-"")"
Range("K2").FormulaR1C1 = "."
Range("L2").FormulaR1C1 =
"=CONCATENATE(RC[-4],RC[-1],RC[-3],RC[-1],RC[-2])"
Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[Hardware
Report.xls]Table'!C1:C12,3,FALSE)"
Range("B2:M2").Select
Selection.AutoFill Destination:=Range("B2:M10000")
Range("B2:M1000").Select
Columns("B:M").EntireColumn.AutoFit

Columns("B:M").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="..", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
end sub