VBA Filtering issue - Large data set guidance?

[Aussie5380]

Hi There,

I have run into a bit of a hiccup with a macro i have written, and was wondering if anyone could provide some advice?

I have a large spreadsheet of data given to our team each fortnight and i have written a macro to import the data off of it into another workbook (there are reasons for this) then filter a few columns based on criteria i have set as below:

Sub Get_Data_For_TIM()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File and Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
    Set OpenBook = Application.Workbooks.Open(FileToOpen)
    OpenBook.Sheets(1).Range("A1:V99999").Copy
            ThisWorkbook.Worksheets("Put TIM Report Here").Range("A1:V99999").PasteSpecial xlPasteValues
        OpenBook.Close False
        
' Filters Team

  Sheets("Put TIM Report Here").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:V99999").AutoFilter Field:=2, Criteria1:="=3", _
        Operator:=xlOr, Criteria2:="=9"
        

' Filters codes for TIM

    Sheets("Put TIM Report Here").Select
    ActiveSheet.Range("$A$1:V99999").AutoFilter Field:=10, Criteria1:=Array( _
        "119", "139", "146", "198", "201", "202", "205", "206", "22", "236", "243", "244", "26", _
        "277", "278", "279", "28", "280", "281", "310", "355", "4", "467", "48", "481", "494", "52", _
        "56", "66"), Operator:=xlFilterValues
        
' Hides unneeded columns

Range("K:V").EntireColumn.Hidden = True

' Highlights the new amount column

Dim Lastrow As Integer
Lastrow = Sheets("Put TIM Report Here").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Put TIM Report Here").Range("Z2:Z" & Lastrow).Interior.Color = vbYellow
    
End If
Application.ScreenUpdating = True
  
End Sub

However I'm having an issue with the last 2 filters i need. Column M and O has a range of "Codes" that is system wide for my job, however i want to filter both of these columns based on a table of "Codes" that I'm looking for (there's 316 of them), on another sheet in the same workbook.

The issue is due to too many variables under the Criteria Array. Is there a way around this? Can i merge a VLOOKUP into the VBA filter to find these codes on another worksheet within the same workbook and have it filter to them only?

I'm at a loss for this bit as everything else works perfect. To try something different I have added 2 columns next to the imported data set that uses a VLOOKUP to see if its listed as one of the codes I'm looking for, but as there are 2 columns, getting a third column with something along the lines of "YES"/"NO" = YES, "NO"/"YES" = YES, "NO"/"NO" = NO to play nicely is driving me bonkers.

Any assistance would be greatly appreciated, and constructive feedback is always welcome.

Shaun

 

[Akuini]

However I'm having an issue with the last 2 filters i need. Column M and O has a range of "Codes" that is system wide for my job, however i want to filter both of these columns based on a table of "Codes" that I'm looking for (there's 316 of them), on another sheet in the same workbook.

The issue is due to too many variables under the Criteria Array.

Utilize a helper column, such as column AC, and then write a macro that searches columns M and O against the code list. Insert a tag, for example, "x," in column AC for every matching row. Perform this operation in an array (in memory) to significantly speed up the process.

Afterward, apply a filter to column AC to display only the rows containing the "x" tag.

If you provide me with a sample workbook (without sensitive data), I would be glad to assist you in writing the macro. You can upload the file to a file-sharing site like Dropbox.com or Google Drive and share the link here.

I have a large spreadsheet of data

About how many rows of data?

Edit:
Instead of "Afterward, apply a filter to column AC to display only the rows containing the "x" tag.", it is easier to just sort col AC then copy the data.

 

[Aussie5380]

Hi Akuini,

Thank you heaps for the above. I appreciate your assistance with this.

I have uploaded a copy of my workbook with the imported data (minus names and employee numbers for privacy reasons) here: https://drive.google.com/file/d/1fu-wXmjQyZuk9c6dRHamWEuJMB4j82lL/view?usp=drive_link

I have created a new Column for the helper column (Y)

I would like to include the blanks in the search in column M (HDA) and for it to look for the salary class's listed on the INFRA CLASS tab. I would also like the same for the Nominal Class column (O), for it to just filter based on the codes listed in the INFRA CLASS tab. Column O wont contain blanks so thats fine.

Column W and X use a VLOOKUP to check is it matches my list of classes.

Please let me know if i can provide any further information to assist.

And thank you so much in advance.

Shaun

 

[Akuini]

I have uploaded a copy of my workbook with the imported data (minus names and employee numbers for privacy reasons) here: Google Drive: Sign-in

I can't download the file, Access Denied.

 

[Aussie5380]

Apologies Akuini, i have just amended the permissions, should be ok

 

[Akuini]

Apologies Akuini, i have just amended the permissions, should be ok

Ok, thanks. I'll try to write the code tonight. It's morning here in Indonesia.

 

[kevin9999]

As far as the Autofilter part of your project goes, please try the following...

Option Explicit
Sub Aussie5380()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Put TIM Report Here")
    Set ws2 = Worksheets("INFRA CLASS")
    Dim a, b, i As Long
   
    'Get the column J array
    a = Array( _
            "119", "139", "146", "198", "201", "202", "205", "206", "22", "236", "243", "244", "26", _
            "277", "278", "279", "28", "280", "281", "310", "355", "4", "467", "48", "481", "494", "52", _
            "56", "66", " ")
   
    'Get the columns M & O array
    b = ws2.Range("A2", ws2.Cells(Rows.Count, "A").End(xlUp))
    b = Application.Transpose(Application.Index(b, 0, 1))
    For i = LBound(b) To UBound(b)
        b(i) = CStr(b(i))
    Next i
   
    'Apply the filters to columns B, J, M and O
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 2, "3", 2, "9"      '<~~ Column B
        .AutoFilter 10, a, 7            '<~~ Column J
        .AutoFilter 13, b, 7            '<~~ Column M
        .AutoFilter 15, b, 7            '<~~ Column O
    End With

End Sub