View original (French)

Delete rows based on a date

fortbelin -  
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
    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 Contributor
      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
      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 Contributor
      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 
      (Range("A" & i).Value <> "")

      You read too fast again, my little sock! :D
      0
    5. chossette9 Contributor
      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
    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
    1. fortbelin Author
      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
      1. fortbelin Author
        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
        2. fortbelin Author
          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 Contributor
            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
            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
        3. fortbelin Author
          Thank you very much,
          it works.

          Sorry, but I'm not a specialist.
          0
          1. chossette9 Contributor
            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
        4. fortbelin Author
          Do you have any idea how to do the same thing with a list of first names?

          Thank you
          0
          1. fortbelin Author
            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
            1. Heremion
              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
              1. fortbelin Author
                THANK YOU VERY MUCH
                IT'S GREAT
                0