Delete rows based on a date
fortbelin
Posted messages
22
Status
Membre
-
fortbelin Posted messages 22 Status Membre -
fortbelin Posted messages 22 Status Membre -
Hello,
Hello everyone.
I need your help with a macro.
It’s about being able to delete rows from an Excel sheet but only if a cell in that row contains a date that is earlier than a reference date.
So for example, delete all rows that contain dates earlier than 12/25/2002
Thank you very much for your help.
Configuration: Windows XP / Safari 535.1
Hello everyone.
I need your help with a macro.
It’s about being able to delete rows from an Excel sheet but only if a cell in that row contains a date that is earlier than a reference date.
So for example, delete all rows that contain dates earlier than 12/25/2002
Thank you very much for your help.
Configuration: Windows XP / Safari 535.1
10 réponses
In this case:
where your reference date is in A1, and your data range is in column A but you can adapt it. If your dates are in row G, replace Range("A65536") with Range("G65536"), and the "A" & i with "G" & i.
Here you go.
--
You should always think about accepting a post that gets you out of trouble.
Private Sub CommandButton1_Click() Dim currentd As Date Dim refdate As Date Dim i, imax refdate = Range("A1").Value imax = Range("A65536").End(xlUp).Row For i = YourRowIndexForDateRange To imax + 1 If (Range("A" & i).Value < refdate) And (Range("A" & i).Value <> "") Then Range("A" & i).EntireRow.Delete i = i - 1 End If Next i End Sub where your reference date is in A1, and your data range is in column A but you can adapt it. If your dates are in row G, replace Range("A65536") with Range("G65536"), and the "A" & i with "G" & i.
Here you go.
--
You should always think about accepting a post that gets you out of trouble.
Hello Fortbelin,
"If a cell in the row...", do you mean a cell, any cell, or a specific cell?
--
You should always think about accepting a post that gets you out of a jam.
"If a cell in the row...", do you mean a cell, any cell, or a specific cell?
--
You should always think about accepting a post that gets you out of a jam.
Hello,
it's actually a very specific cell because I have a date column in my Excel sheet.
So actually, if my date column is in G for example, the macro deletes all the rows that have a date earlier than a reference date in column G.
Thank you very much.
it's actually a very specific cell because I have a date column in my Excel sheet.
So actually, if my date column is in G for example, the macro deletes all the rows that have a date earlier than a reference date in column G.
Thank you very much.
Thank you very much, I will try
Do you have the same thing but based on a list of first names?
For example, remove the lines that contain Pierre or Paul or Jean...
Thank you
Christophe
Do you have the same thing but based on a list of first names?
For example, remove the lines that contain Pierre or Paul or Jean...
Thank you
Christophe
It's me again
I'm sorry, but it's not working, or maybe it's me who isn't doing it right
Do I need to put the code in a module
Private Sub CommandButton1_Click()
Dim currentd As Date
Dim refdate As Date
Dim i, imax
refdate = Range("C1").Value
imax = Range("C65536").End(xlUp).Row
For i = IndiceLigneDeTaPlageDeDate To imax + 1
If (Range("C" & i).Value < refdate) And (Range("C" & i).Value <> "") Then
Range("C" & i).EntireRow.Delete
i = i - 1
End If
Next i
End Sub
Thank you
I'm sorry, but it's not working, or maybe it's me who isn't doing it right
Do I need to put the code in a module
Private Sub CommandButton1_Click()
Dim currentd As Date
Dim refdate As Date
Dim i, imax
refdate = Range("C1").Value
imax = Range("C65536").End(xlUp).Row
For i = IndiceLigneDeTaPlageDeDate To imax + 1
If (Range("C" & i).Value < refdate) And (Range("C" & i).Value <> "") Then
Range("C" & i).EntireRow.Delete
i = i - 1
End If
Next i
End Sub
Thank you
No problem, but there are plenty of tutorials online or fairly comprehensive books in bookstores about VBA, so feel free to browse the internet to find out how to create VBA macros.
Personally, I would recommend visiting the First publishing site (publisher of the "For Dummies" series).
Best regards.
Personally, I would recommend visiting the First publishing site (publisher of the "For Dummies" series).
Best regards.
Thank you very much, I will try to look into it but for now I really don't have the time.
I'm being asked about this at my job...
I'm being asked about this at my job...
In the same way.
Assuming the names to be removed are in column A starting from row 2 on your first sheet and your general list of names is in column A row 2 of your second sheet:
--
You should always think about accepting a post that gets you out of trouble.
Assuming the names to be removed are in column A starting from row 2 on your first sheet and your general list of names is in column A row 2 of your second sheet:
Private Sub CommandButton1_Click() Dim i1, i2 Dim imaxOne Dim imaxTwo imaxOne = Worksheets(1).Range("A65536").End(xlUp).Row imaxTwo = Worksheets(2).Range("A65536").End(xlUp).Row For i1 = 2 To imaxOne For i2 = 2 To imaxTwo If Worksheets(1).Range("A" & i1).Value = Worksheets(2).Range("A" & i2).Value Then Worksheets(2).Range("A" & i2).EntireRow.Delete i2 = i2 - 1 imaxTwo = imaxTwo - 1 End If Next i2 Next i1 End Sub --
You should always think about accepting a post that gets you out of trouble.
your macro is not correct: when deleting rows, you should go from the last to the first, not from the first to the last.
Indeed, suppose we need to delete rows 4 and 5. Starting from the first row, we delete the 4th. The 5th then becomes the 4th, and the 6th becomes the 5th. We continue the code and loop, we reach the 5th row (which was the 6th), and this row is not to be deleted. So we miss row number 5...
In the other direction, starting from the last, we delete the 5th, and the 4th remains the 4th. We loop, and we delete the 4th.
Therefore, it's better to do
Best regards.
But since your imax doesn't change and you're deleting lines, at some point you'll be testing empty lines ;)
(Range("A" & i).Value <> "")You read too fast again, my little sock! :D
It's just nitpicking and trying to optimize the code as much as possible.