How to combine data in a single column from three columns

I have a workbook with two sheets "Asset" & "Combine". How I can combine the row data in a single column from sheet "Asset" (Columns A, B & C) to sheet "Combine" (Column A) .

Please note that "Asset" Sheet (Columns A, B & C) row data will vary some times.

The workbook is attached. Please help me.

Thanks in advance.

Joshi

Hi Joshi

This code appears to do as you require

Option Explicit
Sub combine()
    Dim LR As Long
    Dim Rng As Range
    Dim i As Long
    Dim x As Long
    With Sheets("Combine")
        .Cells.ClearContents
        .Range("A1").Value = "Asset A B C"
        LR = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Row
        For i = 1 To 3
            Set Rng = Sheets("Asset").Range(Sheets("Asset").Cells(2, i), Sheets("Asset").Cells(Rows.Count, i).End(xlUp))
            x = Rng.Rows.Count
            .Range("A" & LR).Resize(x, 1) = Rng.Value
            LR = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Row
        Next i
    End With
End Sub

Let me know of issues.

Hi Joshi

Try this

Option Explicit
Sub combine()
    Dim LR As Long
    Dim Rng As Range
    Dim i As Long
    Dim x As Long
    With Sheets("Combine")
        .Cells.ClearContents
        .Range("A1").Value = "Asset A B C"
        LR = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Row
        For i = 1 To 3
            Set Rng = Sheets("Asset").Range(Sheets("Asset").Cells(2, i), Sheets("Asset").Cells(Rows.Count, i).End(xlUp))
            x = Rng.Rows.Count
            .Range("A" & LR).Resize(x, 1) = Rng.Value
            LR = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Row
        Next i
        .Columns("B:B").Insert
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("B1"), Unique:=True
        .Columns("A:A").Delete
    End With
End Sub

Hi Joshi

You're welcome...glad I could help.