Need macro to conditionally delete and shift cells

[HoosierIT]

I have a very large spreadsheet that the data was not entered in the proper cell for approx 250,000 records.

What I am looking for is a macro that will check the cell value in column, if it is blank or contains Y or just spaces to delete the cell and shift the cells left.

Column names are:

1. First
2. Last
3. Suffix
4. Licensed
5. Email

Essentially I do not need Licensed column and I want to move all the emails over to that column and will later rename it. However, as stated previously, data entry entered the email in the Licensed column for a multitude of rows.

[Olly]

This is a duplicate of http://www.excelforum.com/excel-form...ml#post3764373

Please read the Rules, and don't post duplicate threads.

[HoosierIT]

Attached is an example...

After running the macro, it should be similar to row 6. Containing data in only the first 4 columns if applicable.

[HoosierIT]

Yeah, I reported myself for it and requested the delete of the one posted in formula and functions. I will post an example spreadsheet here.

[Olly]

Try:

Sub foo()
Dim lRow As Long

With Sheet1
    For lRow = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        If InStr(.Cells(lRow, 4).Value, "@") = 0 And InStr(.Cells(lRow, 5).Value, "@") > 0 Then
            .Cells(lRow, 4).Value = .Cells(lRow, 5).Value
        End If
    Next lRow
    .Columns(5).Delete
End With
End Sub

[HoosierIT]

Thanks this did the trick!