CODEWITHSUNDEEP
vbaexcel
jewjitsu
jewjitsu

Reputation: 5

Delete rows in Excel if it doesn't match

Not sure how to do this, I have an Excel sheet that imports emails in 2's - but sometimes it only imports 1 email, so I would like those lines deleted -

1@email1
1@email1
2@email2
2@email2
3@email3 - need deleted since it's only 1 entry
4@email4
4@email4

It's importing from a CSV - I need it to delete the row if it doesn't match, because there should be 2 of the same email addresses imported - so

if A2=A1 it's good  
if A2 doesn't = A1 Delete row A1  
if A4=A3 it's good  
if A4 doesn't = A3 Delete row A3  
if A6=A5 it's good  
if A6 doesn't = A5 Delete row A5

Upvotes: 0

Views: 1666

Answers (3)

Nagendra
Nagendra

Reputation: 11

Assuming data is column A

Sub Deletes()
Dim lastrow As Integer
lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
Range("a1").Select
For i = 1 To lastrow
If ActiveCell.Offset(1, 0).Value <> ActiveCell.Value Then
ActiveCell.EntireRow.Delete    
End If
ActiveCell.Offset(2, 0).Select
Next
End Sub

tested and working fine.

Upvotes: 0

JRQ
JRQ

Reputation: 545

Pretty straightforward in VBA, if I understand the problem. You just want to remove rows that are not followed with an identical email address, right?

a = 1
Do
  b = a + 1
  If Cells(a, 1).Value <> Cells(b, 1).Value Then
    Rows(a).Delete
  Else
    a = a + 2
  End If
Loop Until a > Sheets(1).Rows.Count

Not the most elegant, all purpose solution I'll admit, but quick and easy.

Upvotes: 1

apesa
apesa

Reputation: 12443

Yes you can programmatically remove those values from cells in Excel using something like Apache POI-HSSF It allows you to read and modify Excel worksheets using Java. Look at the Usermodel API it sounds like what you are looking for.

You can also take a look at JExcel Here

Upvotes: 1

Related Questions