View original (French)

Delete rows based on a date

fortbelin Posted messages 22 Status Member -  
fortbelin Posted messages 22 Status Member -
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

10 answers

  1.
    Heremion Posted messages 564 Status Member 102
     
    In this case: 

    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.
    1
    1. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
       
      Hello,

      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 
      For i = Imax+1 to IndiceLigneDeTaPalgeDeDate Step -1


      Best regards.
      0
    2. Heremion Posted messages 564 Status Member 102
       
      Not necessarily since I decrement in the case where I delete, which keeps us on the same line. So, no forgetfulness, but you can indeed do it the other way around, since there would be no need to decrement at all :)
      0
    3. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
       
      Oops, my bad, I read too quickly ^^

      But since your imax doesn't change and you're deleting lines, at some point you'll be testing empty lines ;)
      0
    4. Heremion Posted messages 564 Status Member 102
        
      (Range("A" & i).Value <> "")

      You read too fast again, my little sock! :D
      0
    5. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
       
      Yeah, but you're still going to loop. Sure, it will be super fast, but you're still going to loop on those empty lines.

      It's just nitpicking and trying to optimize the code as much as possible.
      0
  2. Heremion Posted messages 564 Status Member 102
     
    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.
    0
  3. fortbelin Posted messages 22 Status Member
     
    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.
    0
  4. fortbelin Posted messages 22 Status Member
     
    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
    0
  6. fortbelin Posted messages 22 Status Member
     
    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
    0
    1. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
       
      Perhaps we should replace IndiceLigneDeTaPalgeDeDate with a value, right?

      Like 1 if you start on the first line, 2 if you start on the second...
      0
    2. Heremion Posted messages 564 Status Member 102
       
      Yes, I put that there for you to replace it. If your data range starts at line 2, you put 2 :) and there's no need to put this in a module, just a button is enough :)
      0
  7. fortbelin Posted messages 22 Status Member
     
    Thank you very much,
    it works.

    Sorry, but I'm not a specialist.
    0
    1. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
       
      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.
      0
  8. fortbelin Posted messages 22 Status Member
     
    Do you have any idea how to do the same thing with a list of first names?

    Thank you
    0
  9. fortbelin Posted messages 22 Status Member
     
    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...
    0
  10. Heremion Posted messages 564 Status Member 102
     
    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: 

    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.
    0
  11. fortbelin Posted messages 22 Status Member
     
    THANK YOU VERY MUCH
    IT'S GREAT
    0