Concatenate Non Blank Cells

[ssjody]

Hi,

On Sheet1 I want to be able to enter a Value in Column A and another
Value in Column B and have them concatenate in Column C with a } in
between them. I only want this to happen when Column A is Not Blank. If
only Column A has a Value then the result would be ColumnAValue}

I know I can do this with :
=IF(A1="","",CONCATENATE(A1,"}",B1))
but I really want to be able to do this with VBA code instead of a
formula. How do I do this?

Thanks Jody

 

[Soniya]

Range("C1").FormulaR1C1 = _
"=IF(RC1="""","""",CONCATENATE(RC1,""}"",RC2))"

 

[evgny]

Range("C1").FormulaR1C1 = _
"=IF(RC1="""","""",CONCATENATE(RC1,""}"",RC2))"

hi ssjody

try this

Sub a()
Worksheets("Ark1").Activate
Dim colA As Double ' CHECK HOW MANNY ROWS IN COLUMNS A
Dim colB As Double ' CHECK HOW MANNY ROWS IN COLUMNS B
Dim Big As Double
Dim i As Double
colA = Cells(Rows.Count, "B").End(xlUp).Row
colB = Cells(Rows.Count, "B").End(xlUp).Row
If colA > colB Then
Big = colA
Else
Big = colB
End If
For i = 1 To Big
If Range("A" & i) > 0 Then

Range("C" & i) = Range("A" & i) & Range("B" & i)
End If
Next i
End Sub

Regards yngve

 

[ssjody]

Thanks yngve! That's exactly what I wanted.

Jody