PDA

View Full Version : Delete full line under condition



Papayou
10-26-2015, 09:50 AM
Hi guys,

I am new on the forum. I am currently working on my Master Thesis and I need some help for a VBA code. Before I start, please forgive my english, I am not native english speaker but I will try to be as clear as possible.
I have attached a little part of my database so it will be easier to explain.

On coloumn A, I have some dates. On column B, company names.
I would like to delete the entire line if one company appear twice (or more) within 170 days before the previous date (for same coompany). Let's assume company ABC with corresponding date 26/10/2015. I would like to delete all the lines with company ABC for 170 days before 26/10/2015. In the attachment file, lines 19, 21, 27 and 31 should be deleted.

I hope you understand. If not please don't hesitate to tell me.

Thanks alot for your help.

Younes

SamT
10-26-2015, 02:11 PM
Sub VBAX_SamT_RemoveByNameAndDate()
'For help see: http://www.vbaexpress.com/forum/showthread.php?54110

Dim rwCurrentName As Long
Dim rw As Long

For rwCurrentName = 1 To Cells(Rows.Count, "A").End(xlUp).Row Step 2
If rwCurrentName = Cells(Rows.Count, "A").End(xlUp).Row Then Exit Sub
For rw = Cells(Rows.Count, "A").End(xlUp).Row To rwCurrentName + 2 Step -2
If Cells(rw, 2) = Cells(rwCurrentName, 2) Then
If Cells(rw, 1) >= Cells(rwCurrentName, 1) - 170 Then
Rows(rw).Resize(2).Delete
End If
End If
Next rw
Next rwCurrentName
End Sub