VBA to look for an @ symbol and shift rest of row to right if not there

debo007

New Member
Joined
Dec 1, 2018
Messages
2
Hi everyone. I'm having a hard time trying to sort out an Excel VBA. I need to search column D and if there is not an @ symbol anywhere in the cell, shift the rest of the row to the right 1 cell and empty the cell without the @ symbol.
I'm looking to see if the cell has an email address, if it does, leave it alone and if it has something else, shift the remaining cells in that row to the right.

Thanks
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,075
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
Code:
Sub Checkforemail()
   Dim cl As Range
   For Each cl In Range("D2", Range("D" & Rows.Count).End(xlUp))
      If InStr(1, cl, "@", 1) = 0 Then cl.Insert xlToRight
   Next cl
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,075
Office Version
365
Platform
Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,090,396
Messages
5,414,183
Members
403,517
Latest member
richardthevbanoob

This Week's Hot Topics

Top