Delete entirerow if cellvalue in column A has "xxxx" as year

[Eyeson15]

Please take me out of my misery.
I think I'm going insane from google search overload.

I have a workbook with 5000+ entries. It has dates in the format (dd.mm.yy) in Column A.

All I need to do is delete ALL the rows that are not between 2005 and 2010 (inclusive both years)

For example:

(1) 22.02.14
(2) 01.01.07
(3) 02.12.01

So, Rows 1 and 3 would be deleted and 01.01.07 is kept because it is between 2005 and 2010.

Please help me, I got over 6 workbooks to do this and really can't do this manually.

Below is the code I sort of made. Doesn't work ofcourse but brilliant code for crashing computers.

James

Sub DeleteRows()
    
    Dim i As Long
    Dim st As Long
    Dim en As Long
    Dim test As Long

    'Set ranges to four digit year code'
    st = 2005
    en = 2010

    For i = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    
    test = Val(WorksheetFunction.Text(ActiveSheet.Range("A" & i).Value, "yyyy"))
    If test < st Or test > en Then
    ActiveSheet.Range("A" & i).EntireRow.Delete
    End If
    Next i
    
End Sub

 

[Eyeson15]

The code I posted works perfectly with the header... as a matter of fact, it requires the header in order to work. The reason is the way AutoFilter works... without the header, it exempts the first row of data from being hidden (if its date is such that it should be hidden) because that is the row it puts the dropdown triangles in. I haven't looked at the ramifications of your change, but I am sure that my original code works correctly with a header row, it requires no change.

You are right Rick
Your code works perfectly as it is with header row.
I sort of jumped the gun and assumed you didn't know about my header so I edited your code.
But your code is perfect as it is!

James

 

[Eyeson15]

As it turns out the OP doesn't have that much data so the difference will be less, but I also tested with similar data and on my (quite old) machine the post #14 code took a little longer at about 40 seconds.
The code below took 4 seconds on the same data, so may also be worth considering if speed is an issue.

Sub Del_Date_Rows()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long, lBefore As Long, lAfter As Long
  
  lBefore = DateSerial(2005, 1, 1)
  lAfter = DateSerial(2010, 12, 31)
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Formula
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If a(i, 1) < lBefore Or a(i, 1) > lAfter Then   '<- Could be split to make a bit faster
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A2").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

If required, this code could be sped up slightly as well by splitting the commented line into 2 separate comparisons.
Post back if you need that tiny bit of extra speed.

Hello Peter!

Thank you so much. Your code is also flawless and very very fast.

I'm grateful for all your help gents. I really really appreciate it!

James

 

[Peter_SSs]

Hello Peter!

Thank you so much. Your code is also flawless and very very fast.

I'm grateful for all your help gents. I really really appreciate it!

James

You are welcome. At least you have a number of options to choose from.

 

[AkaTrouble]

amazing how much can be achieved in 25 lines of code, also how many scenarios to consider some of which can only be learned from experience.

it will still be a while before i am competing with Rick and Peter but i am here by claiming the slowest Award lol

 

[Rick Rothstein]

As it turns out the OP doesn't have that much data so the difference will be less, but I also tested with similar data and on my (quite old) machine the post #14 code took a little longer at about 40 seconds.
The code below took 4 seconds on the same data, so may also be worth considering if speed is an issue.

Well done Peter... 2.61 seconds for my data setup. That makes your code consistently 10 times faster than mine. I must admit, though, that I am surprised. I have to guess that I am remembering incorrectly, but I could swear that in a previous thread it was "determined" that Filtering and Sorting was faster than reading in an array and processing in memory. Not true I guess... lesson learned.

 

[Tetra201]

@AkaTrouble, @Rick Rothstein, @Peter_SSs:

Hmm, somehow, I got very different test results.

- Computer: CPU i5-2540M @ 2.6 GHz; RAM 4 GB
- Excel: 2010 32-bit
- Setup: column A has dates from 01.01.80 through 22.11.16 (50,000 rows), all values, no formulas;
column B filled with 1's; column C filled with 2's; column D filled with 3's; column E filled with 4's; column F filled with 5's
- Each sub has been modified by adding the following two lines:
t = Timer immediately after Sub xxx()
MsgBox Timer - t immediately before End Sub

- Averaged results (n = 5 for each sub) of deleting 47,809 rows:
Aka's (Post #4) 67 seconds
mine (Post #3) 0.87 seconds
Rick's (Post #14) 0.19 seconds
Peter's (Post #20) 0.26 seconds

 

[AkaTrouble]

@AkaTrouble, @Rick Rothstein, @Peter_SSs:

Hmm, somehow, I got very different test results.

67 seconds desrves a larger font size i still have last place certificate

 

[Rick Rothstein]

@AkaTrouble, @Rick Rothstein, @Peter_SSs:

Hmm, somehow, I got very different test results.

- Computer: CPU i5-2540M @ 2.6 GHz; RAM 4 GB
- Excel: 2010 32-bit
- Setup: column A has dates from 01.01.80 through 22.11.16 (50,000 rows), all values, no formulas;
column B filled with 1's; column C filled with 2's; column D filled with 3's; column E filled with 4's; column F filled with 5's
- Each sub has been modified by adding the following two lines:
t = Timer immediately after Sub xxx()
MsgBox Timer - t immediately before End Sub

- Averaged results (n = 5 for each sub) of deleting 47,809 rows:
Aka's (Post #4) 67 seconds
mine (Post #3) 0.87 seconds
Rick's (Post #14) 0.19 seconds
Peter's (Post #20) 0.26 seconds

Hmm, I tested with 500,000 rows of data whereas you tested with 50,000 rows... maybe the efficiencies of the routines are dependent and variable with the number of rows that have to be processed.

 

[Tetra201]

Hmm, I tested with 500,000 rows of data whereas you tested with 50,000 rows... maybe the efficiencies of the routines are dependent and variable with the number of rows that have to be processed.

This does not seem to be the case. At least with my computer/Excel.

I just ran a scaled-up test. Same set-up with 500,000 rows (dates from 01.01.1910 through 13.12.3278).
The runtimes predictably increased by a factor of ~10:

- mine (Post #3) 8.6 seconds
- Rick's (Post #14) 1.8 seconds
- Peter's (Post #20) 3.1 seconds

 

[Rick Rothstein]

This does not seem to be the case. At least with my computer/Excel.

I just ran a scaled-up test. Same set-up with 500,000 rows (dates from 01.01.1910 through 13.12.3278).
The runtimes predictably increased by a factor of ~10:

- mine (Post #3) 8.6 seconds
- Rick's (Post #14) 1.8 seconds
- Peter's (Post #20) 3.1 seconds

Well, in that case, double hmm! This is strange because when I tested Peter's code on my computer, it definitely ran my test worksheet 10 times faster than my own code whereas you are showing my code to be nearly twice as fast as Peter's code... that is a big discrepancy... I wonder what is at the heart of it?