Create Empty Cell after Cell Containing @

[hl2qs]

Hello,

I have a bunch of contacts I imported into Excel. Most of contacts are separated by an empty space, but some contacts are stuck together. Like this:

Bob
119 Cresap
Scottsville, VA
bob@virginia.edu
Jane
126 Stadium
Staunton, Va
Jane87@yahoo.com


Since the last cell of each contact contains their email address, is it possible to insert a blank cell after each cell containing @?

Thanks
Hamilton

 

[Gary McMaster]

Hi Hamilton,

Try this on a backup copy of your work:

Public Sub Test()

Dim oFound As Range
Dim sAddress As String

Set oFound = ActiveSheet.UsedRange.Find("@", MatchCase:=False, lookat:=xlPart)
If Not oFound Is Nothing Then
    sAddress = oFound.Address
    Do

        If oFound.Offset(1, 0).Value <> "" Then
            oFound.Offset(1, 0).Insert shift:=xlDown
        End If
        
        Set oFound = ActiveSheet.UsedRange.FindNext(oFound)
        
    Loop While Not oFound Is Nothing And oFound.Address <> sAddress
End If

End Sub

Gary

 

[hl2qs]

Worked beautifully. It didn't even create extra spaces were they already existed.

Thanks
Hamilton