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.
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