From column A and column B data into 1 line pair

Hello all!
I need a VBA Code to solve my problem as attachments.
Please see the file.
thank you!

Note: If Column B has a value of 0 is not paired

Try:-

Sub MG31Jan46
Dim Rng As Range, Dn As Range, Stg As String
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
    If Not Dn.Offset(, 1) = 0 Then
        Stg = Stg & Dn.Value & ":" & Dn.Offset(, 1).Value & "Kg" & ", "
    End If
Next Dn
Range("I1").Value = Stg
End Sub

Regards Mick

Originally Posted by MickG

Try:-

Sub MG31Jan46
Dim Rng As Range, Dn As Range, Stg As String
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
    If Not Dn.Offset(, 1) = 0 Then
        Stg = Stg & Dn.Value & ":" & Dn.Offset(, 1).Value & "Kg" & ", "
    End If
Next Dn
Range("I1").Value = Stg
End Sub

Regards Mick

Your answer so fast!
I want to ask you is that I want to delete the character "," at the end of the line you just created, then how?
thank you!

Originally Posted by Richard Buttrey

Hi,

Enter this to C2, and copy it down. Then select C16, copy it and paste it back as a value.

Formula:

=C1&A2&":"&B2&"kg"

Thank you!
This may seem a long way, further still appear 0kg value in line pairing?

Try this:-

Sub MG31Jan15
Dim Rng As Range, Dn As Range, Stg As String
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
    If Not Dn.Offset(, 1) = 0 Then
        Stg = Stg & ", " & Dn.Value & ":" & Dn.Offset(, 1).Value & "Kg"
    End If
Next Dn
Range("I1").Value = Mid(Stg, 3)
End Sub

Regards Mick

Originally Posted by MickG

Try this:-

Sub MG31Jan15
Dim Rng As Range, Dn As Range, Stg As String
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
    If Not Dn.Offset(, 1) = 0 Then
        Stg = Stg & ", " & Dn.Value & ":" & Dn.Offset(, 1).Value & "Kg"
    End If
Next Dn
Range("I1").Value = Mid(Stg, 3)
End Sub

Regards Mick

Standard!
thank you very much