VBA rows not being deleted correctly

[Dimitris254]

I have a worksheet with 10 columns, two of which (J and K).
J column takes various text values, one of which is "External Research".
K column takes year values (they are formatted as "General" type), starting 2001.

I want to delete the whole row, when J is "External Research" or K is <=2015 (so keep only the current year 2016 and later).

i tried this, but the macro takes just too long (total rows ~50k) :

Option Explicit

Sub clear_years_inputtype()

Dim lastRow As Long
Dim i As Long
Dim wsRAWDATA As Worksheet

Application.ScreenUpdating = False

Set wsRAWDATA = ThisWorkbook.Worksheets("RAWDATA")

With wsRAWDATA
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow
        If .Cells(i, "J") = "External Research" Or .Cells(i, "K") <= 2015 Then
            .Rows(i).EntireRow.Delete
            lastRow = lastRow - 1
            i = i - 1
        End If
    Next i
End With

Application.ScreenUpdating = True


End Sub

What is a better way to do this?

 

[Momentman]

OMG that was unreal, so fast...

one question while i'm working on removing the redundant years; can i put multiple criteria like Criteria = 2010, 2011, 2012 ?

@Cbaker: Thats what I was talking about
The autofilter method only allows for 2 criteria (per field/column) though but how exactly do you want to use the years you stated above?

 

[Dimitris254]

the "Years" columns contains more years, for example from 2005 up to 2020.

I'd like to keep only the rows for 2016, 2017, 2018, 2019 and 2020.

In other words, delete anything <=2015

I could always run the same macro 10 times (changing only the year criterion), but it doesn't sound optimal

 

[cbaker1991]

Ahh I understand,

If you change the column of the auto filter to "11" which should be column K and the criteria as "<" & "2016" this should work.

The new code should effectively do 2 filters first one removing any in column J with "External Research" and the second removing anything in column K less than 2016.

Potential problems to watch for though if there is an input error in the year e.g. only wrote 201 instead of 2016-2020 this will also be removed, also in a few months the code will need to be changed to 2017.

Changing the code to 2017 can be removed if the current year is referred to somewhere in the file and then this criteria could be directed to this cell making the code slightly more dynamic.

 

[Dimitris254]

thanks again cbaker, i also linked the year to another worksheet as you suggested

Option Explicit

Sub clear_inputtype_years()

Dim lastRow As Long
Dim wsRAWDATA As Worksheet
Dim wsARRAYS As Worksheet

Set wsRAWDATA = ThisWorkbook.Worksheets("RAWDATA")
Set wsARRAYS = ThisWorkbook.Worksheets("ARRAYS")

Application.ScreenUpdating = False
 
With wsRAWDATA
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Range("$A$1:$O$" & lastRow).AutoFilter Field:=10, Criteria1:="External Research"
    Range("$A$1:$O$" & lastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilterMode = False
    
    Range("$A$1:$O$" & lastRow).AutoFilter Field:=11, Criteria1:="<" & wsARRAYS.[I2]
    Range("$A$1:$O$" & lastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilterMode = False
End With

Application.ScreenUpdating = True


End Sub

one quick question: is it really necessary to deactivate auto filtering before running the next "clean up" (for years) ?