Concatenate 2 columns into 1 column with seperator

[STEEL010]

Hi There,

I'm trying to concatenate 2 columns ("A") and ("B") into 1 column ("A") with an ";" separator via VBA.
I have already have a code that doing stuff already and I want to add the concatenate function in to it, can somebody help?

all have to comeback into column A.


Sheets("Sheet2").Copy
Sheets("Sheet2").Range("A2:F101").Copy
Sheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet2").Range("A1:H1").Delete Shift:=xlUp
Sheets("Sheet2").Range("C1:F101").Delete Shift:=xlToLeft
here I want the concatenate function


Greetings,
Steel010

 

[jmacleary]

This snippet will do that - note that ALL rows will have the semicolon inserted. If you want to ignore blank A and B rows, extra coding will be needed.

For i = 1 To 101
    Range("A" & i).Value = Range("A" & i).Value + ";" + Range("B" & i).Value
Next i

 

[KOKOSEK]

dim cell as range
for each cell in Range("A2:A101")
cell.value= cell.value & ";" & cell.offset(0,1).value
next cell

 

[Fluff]

Another way

   With Range("A2:A101")
      .Value = Evaluate("if({1}," & .Address & "& ""; "" & " & .Offset(, 1).Address & ")")
   End With

 

[STEEL010]

Fluff,

yours works, sorry jmacearly yours not, thanks anyway.

Fluff,

one thing .Address is that something I must change? because its doing its job but it let column B with value.

Another way

   With Range("A2:A101")
      .Value = Evaluate("if({1}," & .Address & "& ""; "" & " & .Offset(, 1).Address & ")")
   End With

 

[jmacleary]

OK Steel thanks for the feedback - however it worked on my machine fine - could you tell me what 'didn't work'. If its because column B still has values, that is what seemed to be your request. If you want column B to be blank, then insert
Range("B" & i).Value="" in the loop.

 

[Fluff]

Do you want to clear the contents is col B, or delete the entire column?

 

[STEEL010]

JMAclearly,

it gives me an type mismatch error.....


@ Fluff,

delete entire column

 

[Fluff]

How about

Sub Steel010()
   With Range("A2:A101")
      .Value = Evaluate("if({1}," & .Address & "& ""; "" & " & .Offset(, 1).Address & ")")
   End With
   Columns(2).Delete
End Sub

 

[STEEL010]

Fluff,

now nothing runs? and still it must be written following up my already written macro , not in with an extra sub.

is there a way