I have a macro that I use to strip out unwanted columns. For example I have
my wanted data in column A, B and C. What I want to do is add some text to
column D and have it copied down to the end of the file. The problem I have
is that all the files are different sizes. How can I specifiy the range in
my code to copy the text down to the end of the file instead of a certain
cell?

T.I.A.


Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(3, 2), Array(17, 9), Array(37,
1), Array(45, 9), _
Array(56, 9), Array(58, 9), Array(60, 9), Array(68, 9), Array(82,
9), Array(90, 9), Array( _
99, 9), Array(108, 9), Array(117, 9), Array(126, 9), Array(135, 9),
Array(144, 9), Array( _
152, 9), Array(160, 9), Array(169, 9), Array(178, 9), Array(187, 9),
Array(195, 9), Array( _
203, 9), Array(212, 9), Array(216, 9), Array(240, 9), Array(258, 9),
Array(263, 9), Array( _
286, 9), Array(294, 9)), TrailingMinusNumbers:=True
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.ColumnWidth = 26
Range("D2").Select
ActiveCell.FormulaR1C1 = "TEXT"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D16355")
Range("D2:D16355").Select

Dayton,

Give this a whirl. Watch for wrap-around

Dim cRows As Long, i As Long

Columns("A:A").TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(3, 2), Array(17, 9), Array(37,
1), Array(45, 9), _
Array(56, 9), Array(58, 9), Array(60, 9), Array(68, 9), Array(82,
9), Array(90, 9), Array( _
99, 9), Array(108, 9), Array(117, 9), Array(126, 9), Array(135, 9),
Array(144, 9), Array( _
152, 9), Array(160, 9), Array(169, 9), Array(178, 9), Array(187, 9),
Array(195, 9), Array( _
203, 9), Array(212, 9), Array(216, 9), Array(240, 9), Array(258, 9),
Array(263, 9), Array( _
286, 9), Array(294, 9)), TrailingMinusNumbers:=True
With Columns("C:C")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.ColumnWidth = 26
End With

cRows = Cells(Rows.Count, "A").End(xlUp).Row
With Range("D2")
.FormulaR1C1 = "TEXT"
.AutoFill Destination:=Range("D2:D" & cRows)
End With
Range("D2:D" & cRows).Select

--

HTH

Bob Phillips

"dayton" wrote in message
...
I have a macro that I use to strip out unwanted columns. For example I
have
my wanted data in column A, B and C. What I want to do is add some text to
column D and have it copied down to the end of the file. The problem I
have
is that all the files are different sizes. How can I specifiy the range in
my code to copy the text down to the end of the file instead of a certain
cell?

T.I.A.


Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(3, 2), Array(17, 9), Array(37,
1), Array(45, 9), _
Array(56, 9), Array(58, 9), Array(60, 9), Array(68, 9), Array(82,
9), Array(90, 9), Array( _
99, 9), Array(108, 9), Array(117, 9), Array(126, 9), Array(135,
9),
Array(144, 9), Array( _
152, 9), Array(160, 9), Array(169, 9), Array(178, 9), Array(187,
9),
Array(195, 9), Array( _
203, 9), Array(212, 9), Array(216, 9), Array(240, 9), Array(258,
9),
Array(263, 9), Array( _
286, 9), Array(294, 9)), TrailingMinusNumbers:=True
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.ColumnWidth = 26
Range("D2").Select
ActiveCell.FormulaR1C1 = "TEXT"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D16355")
Range("D2:D16355").Select