AutoFilter using a vba generated array

[phuelgod]

Hello,

I'm trying to automate some of the updating I do within an excel 2013 workbook. Each month, I refresh the rolling-6 month data for the past 3 years and add a new rolling-6 months of data. to do this, I manually filter and delete the respective periods. As an example, if I'm doing the data update for August 2018, I'll delete the data for August 2015, February 2016, August 2016, February 2017, August 2017, and February 2018. I'll then add new data for August 2015, February 2016, August 2016, February 2017, August 2017, February 2018, and August 2018.

I thought I could automate this by...
- use a UserForm to select the earliest month and year to delete
- generate an array using a for...next loop, adding 6 months to the value with each iteration
- autofilter using the array as criteria and deleting the visible cells.

Unfortunately, it doesn't want to work. Here's the code I'm using...

Public dtFirst As String

Sub Six_Month_Delete()
'
' Delete most recent 6-month intervals
'

Dim arrFilter(6) As String
Dim iMonthAdd As Integer
Dim iSheet As Integer
Dim iRow As Single
Dim strRange As String
Dim myBook As Workbook

Set myBook = ActiveWorkbook

UserForm1.Show

arrFilter(0) = dtFirst
For iMonthAdd = 1 To 6
    arrFilter(iMonthAdd) = Format(DateAdd("m", iMonthAdd * 6, DateValue(dtFirst)), "m/d/yyyy")
Next iMonthAdd
    
For iSheet = 1 To 3

    With myBook.Sheets(iSheet)
        .Activate
        iRow = .Range("a1").End(xlDown).Row
        strRange = "$A$1:$S$" & iRow
        .Range(strRange).AutoFilter
        .Range(strRange).AutoFilter Field:=2, Operator:=xlFilterValues, Criteria1:=arrFilter()
        .Range(strRange).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Range(strRange).AutoFilter
    End With
        
Next iSheet

End Sub

I've checked the array output, and it contains the strings I think it should contain...

arrFilter(0) = "8/1/2015"
arrFilter(1) = "2/1/2016"
...
arrFilter(5) = "2/1/2018"


Any assistance you can provide will be greatly appreciated.

-Frank

 

[dmt32]

Hi,
just an idea and assuming dates to be filtered are real dates then try converting your array dates to long data type & see if this resolves

Dim arrFilter(1 To 6) As Long
For iMonthAdd = 1 To UBound(arrFilter)
    arrFilter(iMonthAdd) = CLng(DateAdd("m", iMonthAdd * 6, DateValue(dtFirst)))
Next iMonthAdd

Dave

 

[phuelgod]

Thanks Dave,

I had tried using different data types, but kept getting errors with the autofilter; I checked on this and found that the autofilter criteria must be a string.

I wound up running a record macro to see how Excel was populating the search criteria. I don't exactly know why, but Excel generated an array Criteria2 instead of Criteria1. I made the following adjustments to my code and it worked.

Public dtFirst As String

Sub Six_Month_Delete()
'
' Delete most recent 6-month intervals
'

Dim arrFilter(13) As Variant
Dim iMonthAdd As Integer
Dim iSheet As Integer
Dim iRow As Single
Dim iColumn As Integer
Dim strRange As String
Dim myBook As Workbook

Set myBook = ActiveWorkbook

UserForm1.Show

<font color = red>arrFilter(0) = 1
arrFilter(1) = dtFirst
For iMonthAdd = 2 To 12 Step 2
    arrFilter(iMonthAdd) = 1
    arrFilter(iMonthAdd + 1) = Format(DateAdd("m", iMonthAdd * 3, DateValue(dtFirst)), "m/d/yyyy")
Next iMonthAdd</font>
    
For iSheet = 1 To 3

    With myBook.Sheets(iSheet)
        .Activate
        iColumn = .Range("1:1").Find("R6 Date").Column
        iRow = .Range("a1").End(xlDown).Row
        strRange = "$A$1:$S$" & iRow
        .Range(strRange).AutoFilter
        .Range(strRange).AutoFilter Field:=iColumn, Operator:=xlFilterValues, <font color = red>Criteria2:=arrFilter()</font>
        .Range(strRange).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Range(strRange).AutoFilter
    End With
        
Next iSheet

End Sub

 

[dmt32]

well done always best when resolve these things yourself

Dave