Count Dates & String Dates After Given Date

  • The attached workbook has dates in column C, although some of these dates are just strings.
    I'm trying to write some vba that will tell me how many of the cells in column C contain a date (or looks like a date) that is greater than (after) the real date in cell G1.
    At the moment I loop through the cells in column C and can ascertain, which dates can be counted, then copy one row over at a time, but I'm looking for a slicker (perhaps one-liner) answer, perhaps by copying a block of rows in one go. The aim is to copy those rows to another sheet. There are many more rows than in the attached, and many sheets to process, and I have no control over the format of the dates/strings in column C. Currently it takes about 20 seconds to copy over the necessary rows, but I'm looking for it to happen much more quickly; current thoughts are to sort on column C (sorting on column C anything that looks like a number as a number - which has it's own problems!), have a count of dates satisfying the criterion (say using a worksheet formula such as COUNTIF or SUMPRODUCT, perhaps also using EVALUATE) then copy a block of rows in one go.
    Any suggestions?
    regards, Pascal
    ps. not very relevant, but the existing code is something like this which highlights rather than copyies the rows(included in the attached):

    Sub test()
    With ThisWorkbook.Sheets("Sheet1")
    .Range("C2:C11").Interior.ColorIndex = xlNone
    lastdate = .Range("G1").Value
    For Each cll In .Range("C2:C11").Cells
      If DateValue(cll.Value) > lastdate Then cll.Interior.ColorIndex = 37
      Debug.Print cll.Value, DateValue(cll.Value), lastdate, DateValue(cll.Value) > lastdate
    Next cll
    End With
    End Sub
  • Re: Count Dates & String Dates After Given Date

    Can this be done without looping? (Takes time)
    regards, Pascal

  • Re: Count Dates & String Dates After Given Date

    Convert all text dates to TRUE dates; can THIS be done without looping? The data is a history file, added to from time to time, I 'm not supposed to amend it, but I can if I don't save the file, so would have to convert the dates each time I was running the macro. If it needs a loop to do this, then I doubt there would be much speed advantage over what I'm already doing.
    regards, Pascal

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!