Concatenation Variable Dilemma

[Bass71]

Hi:

I am trying to concatenate between 2-20 cells in a spreadsheet using a macro. The problem is that each time I run a previous macro, the number of cells in the row may differ. I'm thinking I should go from the rightmost cell and work left. I was thinking of taking the cell.count method and somehow tying that number to an object and then working n-1 in the concatenation formula until it equals 1 i.e. the first column. Here's the code:

Dim number As Integer
Dim area As Range
number = number + Selection.Cells.Count

Range("A1&quot.Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select


ActiveCell.FormulaR1C1 = _
&quot;=CONCATENATE(RC[-3],RC[-2],RC[-1])&quot;???? <<I need to somehow express the 'number' object in the RC as a variable until it runs into column A.>>

Thanks for your help, you guys are great.............Rickie

 

[SBendBuckeye]

Once you have executed this line:

Selection.End(xlToRight).Select

I believe Selection.Column should return the Column Number. Then you can just do a loop until Column Number = 1.

Good Luck!

 

[calculus]

Why not just concatenate the cell values?

MyString = &quot;&quot;

For y = 1 to 20
MyString = MyString & Trim(cells([Row],Y).value)
Next y

This will add the value of the next cell to the right to the string that is being built. The &quot;TRIM&quot; function will remove leading and trailing spaces, and therefore ignore null cells and cells of all spaces.

Hope this helps,

calculus

 

[Zathras]

If you really need the formula in the cell (and not just concatenated strings) then you could use this:
(It assumes that you have inserted a new blank column A to put the formula in, and concatenates column B thru to the end.)
[blue]

Option Explicit

Sub BuildAllCsv()
Dim rngColumnB As Range
Dim c As Range
Dim rngLastCell As Range
Dim rngRow As Range
  With ActiveSheet
    Set rngColumnB = Intersect(Range("B:B"), .UsedRange)
    For Each c In rngColumnB
      Set rngRow = Range(Cells(c.Row, 2), Cells(c.Row, 256).End(xlToLeft))
      BuildCSVFormula c.Offset(0, -1), rngRow
    Next c
  End With
  Set rngColumnB = Nothing
  Set rngRow = Nothing
End Sub

Sub BuildCSVFormula(Destination As Range, Source As Range)
Dim c As Range
Dim nCol As Integer
Dim sFormula As String
  nCol = 1
  For Each c In Source
    If c.Column = Source.Column Then
      sFormula = "=CONCATENATE(RC[1]"
    Else
      nCol = nCol + 1
      sFormula = sFormula + "," + "RC[" & nCol & "]"
    End If
  Next c
  Destination.FormulaR1C1 = sFormula & ")"
End Sub

[/color]
Alternatively, if you want values instead of formulas, use this function:
[blue]

Sub BuildCSVString(Destination As Range, Source As Range)
Dim c As Range
  For Each c In Source
    If c.Column = Source.Column Then
      Destination.Value = c.Value
    Else
      Destination.Value = Destination.Value + c.Value
    End If
  Next c
End Sub

[/color]
And call that instead of BuildCSVFormula from the main routine.