Macro -- Duplicate Row if Semicolon is found in Cell

Use the Split function in VBA. If the count > 1 then you have 2 email addresses

Would you split what's in column K so each email address would be in it's own row?

Would there ever be more than one email address in the cell?

Dim Tokens() As String

Tokens = Split(Sheets("SheetName").Range("K").value, ";")

If Tokens.Length > 1 Then
    'Put token 2 on its own row
End If

Select allOpen in new window

Hi Norie,

Each email would be in its own cell and on a seperate row, but would contain info from its previous row. Hope that makes sense!

 Trying to find the most effcient way of doing this

Sorry

Dim Tokens() As String

Tokens = Split(Sheets("SheetName").Range("K").value, ";")

If Tokens.Count> 1 Then
    'Put token 2 on its own row
End If

Select allOpen in new window

Thanks for everyone's input!  Subodh your code worked perfectly!

You're welcome Moxie! Glad it worked. :)

If I were to include commas in addtion to semicolons. Would the code look like this:

  If InStr(Cells(i, "K"), ";") > 0 Or InStr(Cells(i, "K"), ",") > 0 Then
        str = Split(Cells(i, "K"), "; ")
        n = UBound(str)
        Rows(i).Copy
        Rows(i + 1).Resize(n).Insert Shift:=xlDown
        Cells(i, "K").Resize(n + 1).Value = Application.Transpose(str)
    End If


Sorry relatively new to programming!

Yeah the IF condition is okay but still the cell content is split based on ; delimiter. Why would you have both the commas and semicolon in the same cell as delimiters?

I noticed the cells are also separated by a comma as I was working on the spreadsheet.  Thanks for following up.

The chosen answer addressed the original question.