cacatenation code

[Matthew Kramer]

I have two questions about cacatenation in excel:

1) what would be the VB code for inserting a column and putting in it a
cacatenation of all the columns to the right when the number of columns
is changing from worksheet to worksheet. In some worksheets, the number
of columns to cacatenate may be four, in others 5, etc.

2) how could cacactentation be done for only those columns with specific
headings in the first row. For example, cacatenate only those columns
with headings "company" and "country". Some code on this would be
appreciated.

Thanks.

Matthew Kramer

 

[Tom Ogilvy]

Sub Tester1()
Dim rng As Range, rng2 As Range
Dim rng1 As Range, cell As Range
Dim sStr As String
Set rng = Cells(ActiveCell.Row, "IV").End(xlToLeft)
Set rng1 = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
ActiveCell.EntireColumn.Insert
Set rng2 = Range(rng1(1), rng)
Debug.Print rng2.Address
For Each cell In rng2
sStr = sStr & cell.Address(0, 0) & "&"
Next
sStr = "=" & Left(sStr, Len(sStr) - 1)
rng1.Offset(0, -1).Formula = sStr
End Sub

Sub Selectedcolumns()
Dim rng As Range, rng2 As Range
Dim rng1 As Range, cell As Range
Dim sStr As String, sStr1 as String
Set rng = Cells(ActiveCell.Row, "IV").End(xlToLeft)
Set rng1 = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
ActiveCell.EntireColumn.Insert
Set rng2 = Range(rng1(1), rng)
Debug.Print rng2.Address
For Each cell In rng2
sStr1 = lcase(cells(1,cell.column))
if sStr1 = "country" or sStr1 = "company" then
sStr = sStr & cell.Address(0, 0) & "&"
end if
Next
if len(trim(sStr)) = 0 then
rng1.Offset(0,-1).EntireColumn Delete
exit sub
End if
sStr = "=" & Left(sStr, Len(sStr) - 1)
rng1.Offset(0, -1).Formula = sStr
End Sub

 

[Matthew Kramer]

Thanks Tom.

Matthew