HI Joel,

This greats...
when i put data in column B or C, data from column A on column D disappear.
I have to save and close from xls sheet to update my data.
Please advice again.

Regards
lyn

"Joel" wrote:

You could make the code a worksheet change and clear column D before the code
is run

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Columns("D").ClearContents

'Copy column A to Column d
Columns("A").Copy Destination:=Columns("D")

NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1
LastRowB = Range("B" & Rows.Count).End(xlUp).Row
Set CopyRange = Range("B1:B" & LastRowB)
CopyRange.Copy Destination:=Range("D" & NewRowD)

NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1
LastRowC = Range("C" & Rows.Count).End(xlUp).Row
Set CopyRange = Range("C1:C" & LastRowC)
CopyRange.Copy Destination:=Range("D" & NewRowD)

Application.EnableEvents = True
End Sub


"Lyn" wrote:

HI Joel,

This is perfect formula, and thanks a lot.
If you not mind, I have another question. I would like put data for example
in column A and in the column D will be automatically update also. Cause with
this formula I have to close my excel file and open again to update data in
column D
Could you please advice this one.

Regards
lyn


"Joel" wrote:

Sub CombineColumns()

'Copy column A to Column d
Columns("A").Copy Destination:=Columns("D")

NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1
LastRowB = Range("B" & Rows.Count).End(xlUp).Row
Set CopyRange = Range("B1:B" & LastRowB)
CopyRange.Copy Destination:=Range("D" & NewRowD)

NewRowD = Range("D" & Rows.Count).End(xlUp).Row + 1
LastRowC = Range("C" & Rows.Count).End(xlUp).Row
Set CopyRange = Range("C1:C" & LastRowC)
CopyRange.Copy Destination:=Range("D" & NewRowD)


End Sub

"Lyn" wrote:

Dear Expert,

I have 3 columns data (for example column A, B and C) and I would like copy
to become one column data only, the example as below:

A B C D
Tomato 1 a Tomato
Sugar 2 b Sugar
3 c 1
d 2
3
a
b
c
d

Could you please advice me

Regards
Lyn