PDA

View Full Version : Quick way for removing rows



YasserKhalil
02-16-2017, 09:25 PM
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

p45cal
02-17-2017, 04:48 AM
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

YasserKhalil
02-17-2017, 06:07 AM
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

p45cal
02-17-2017, 06:30 AM
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

YasserKhalil
02-17-2017, 09:58 AM
That's wonderful. exactly as needed
Thank you very much for great help

Best Regards