Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cacatenation code
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cacatenation code
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 -- Regards, Tom Ogilvy "Matthew Kramer" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cacatenation code
Thanks Tom.
Matthew *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Cacatenation and SUMPRODUCT | Excel Discussion (Misc queries) | |||
option buttons run Click code when value is changed via VBA code | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |