Consulting

Results 1 to 5 of 5

Thread: Quick way for removing rows

  1. #1

    Quick way for removing rows

    Hello everyone
    I have large amounts of emails in one sheet but not in one column and I need a quick way to remove all the rows that don't have emails ..
    the problem is that the emails are not in one column ...
    Thanks advanced for help

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,876
    Sub blah()
    Dim DelRng As Range
    For Each rw In ActiveSheet.UsedRange.Rows
      If Application.CountA(rw) = 0 Then
        If DelRng Is Nothing Then Set DelRng = rw Else Set DelRng = Union(DelRng, rw)
      End If
    Next rw
    If Not DelRng Is Nothing Then DelRng.EntireRow.Delete
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Thanks a lot for reply
    I thought the code would contain the search for specific character which is @ to make sure it contains an email or not
    Generally I think this is very good start solution
    Regards

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,876
    Sub blah()
    Dim DelRng As Range
    For Each rw In ActiveSheet.UsedRange.Rows
      If rw.Find("@", lookat:=xlPart, LookIn:=xlFormulas, searchformat:=False) Is Nothing Then
        If DelRng Is Nothing Then Set DelRng = rw Else Set DelRng = Union(DelRng, rw)
      End If
    Next rw
    If Not DelRng Is Nothing Then DelRng.EntireRow.Delete
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    That's wonderful. exactly as needed
    Thank you very much for great help

    Best Regards
    Last edited by YasserKhalil; 02-17-2017 at 10:40 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •