0

enter image description here

I want to fully automate the process of cleaning up exported data. I want to:

  • Move the data in the overflow rows into their prospective column. I have tried the following code in VB. (This is trying to identify the @ symbol in the emails and respectively move all email address two places to the right).

Sub qwerty()
    Dim D As Range, r As Range
    Set D = Intersect(ActiveSheet.UsedRange, Range("D:D"))

For Each r In D If Left(r.Text, 2) = "@" Then r.Copy r.Offset(0, 1) r.Clear End If Next r End Sub

  • Once the data is in the correct column I need to automate the movement into the correct row. I can easily have them shift up but if one contact doesn't have an email address (as an example) then the emails will be in the wrong rows when they shift up.
0

Use this code:

Sub qwerty()
    Dim y As Integer, x As Integer

    y = 2
    Do Until Cells(y, 4) = ""
        If Cells(y, 3) = "" Then
            Select Case Left(Cells(y, 4))
                Case "[E": x = 6
                Case "[H": x = 7
                Case "[M": x = 8
                Case "[A": x = 9
                Else: x = 10
            End Select
            Cells(y - 1, x) = Cells(y, 4)
            Rows(y).EntireRow.Delete
        Else
            y = y + 1
        End If
    Loop
End Sub

Some remarks on your original VBA:

  • "This is trying to identify the @ symbol in the emails ...": left(r.Text,2) selects the first two character of a string, it will never be equal to a one character long string ("@")! (my code decides location based on first character after [)
  • "and respectively move all email address two places to the right" - your code is OFFSET(0,1), which is just one column to the right, always check consistency of your code.
  • "Once the data is in the correct column I need to automate the movement into the correct row" - my code does both in the same step, beside shorter code, it's even more simpler.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.