Help remove unwanted semi colons after last value in a string

[DaleKeel]

First User Clicks button (Peace Out) to clear all data.

Sub Delete_Entries()

' Delete Macro

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("C2").Select
Selection.ClearContents
End Sub

The User Manually copies data from a source and then pastes Values from source to cell A2...

Sub Paste_Values()
'
' Paste_Values Macro
'

'
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Then User click Button (Abracadabra) and makes a single string of data. The problem is that it leaves unwanted semi colons at the end.
I do not know how to get rid of them. Please help. Thanks


Sub vba_concatenate()

Dim rng As Range
Dim i As String
Dim SourceRange As Range

Set SourceRange = Range("A2:A100")

For Each rng In SourceRange
i = i & rng & "; "
Next rng
Range("c2").Value = Trim(i)

End Sub

 

[Michael M]

rng = Left(rng, Len(rng) - 1)

 

[DanteAmor]

Try this:

Sub vba_concatenate()
  Dim rng As Range
  Dim i As String
  Dim SourceRange As Range
 
  Set SourceRange = Range("A2", Range("A" & Rows.Count).End(3))
 
  For Each rng In SourceRange
    If rng.Value <> "" Then i = i & rng.Value & "; "
  Next rng
   
  If i <> "" Then
    Range("c2").Value = Trim(Left(i, Len(i) - 2))
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[DaleKeel]

Sub vba_concatenate() Dim rng As Range Dim i As String Dim SourceRange As Range Set SourceRange = Range("A2", Range("A" & Rows.Count).End(3)) For Each rng In SourceRange If rng.Value <> "" Then i = i & rng.Value & "; " Next rng If i <> "" Then Range("c2").Value = Trim(Left(i, Len(i) - 2)) End If End Sub

This WORKED. Thank you VERY much for your time and knowledge.

 

[Fluff]

In future please mark the post that contains the solution, not you post saying it works. Thanks