Advanced Filter VBA

[lwarren30]

Hello,
I'm using an advanced filter vba to search for data from within a large data set onto another sheet. I've worked this before but this time it is not working.

Range("Sheet_K_2D_FinancialInput").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheet15.Range("BV4:BZ5"), CopyToRange:=Sheet15.Range("AV5:BS5"), Unique:=False

I can provide screenshots if you are familiar with this code.

 

[Akuini]

It's not clear to me, do you want to copy the result to the same sheet or another sheet?

I can provide screenshots if you are familiar with this code.

That would be good.
Or you could upload a sample workbook to a site such as dropbox.com then put the link here.

 

[lwarren30]

Copying results to another sheet. Let me looking to uploading it.

 

[lwarren30]

I've sent you a private message with a link.

It's not clear to me, do you want to copy the result to the same sheet or another sheet?



That would be good.
Or you could upload a sample workbook to a site such as dropbox.com then put the link here.

 

[Akuini]

Actually you can't do that, it against Forum rule no 4 http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html.

Just upload the file to a site then put the link here.

 

[Kenneth Hobson]

I made a simple file that makes testing filters easy. I show both autofilter and advancedfilter methods in this file: https://www.dropbox.com/s/d9cqr3ub6tt149d/Copy2ColsToOtherSheet.xlsm?dl=0

Here is the advancedfilter where button is in activesheet "AdvancedFilter" as explained in post #8 . This filters twice since the autofilter method does it twice to get two separate columns.

Private Sub CommandButton1_Click()
    Sheets("One").Range("A1").CurrentRegion.AdvancedFilter _
    xlFilterCopy, Range("AdvancedFilter!Criteria"), Range("A1"), False
    
    Sheets("One").Range("A1").CurrentRegion.AdvancedFilter _
    xlFilterCopy, Range("AdvancedFilter!Criteria"), Range("F1"), False
End Sub

For giggles, the autofilter method for two columns not in same order:

'https://www.excelforum.com/excel-programming-vba-macros/1288508-if-then-code-running-very-slow.html
'Similar to vcoolio's Test()
Sub Main()
    Dim ws As Worksheet: Set ws = Sheets("AutoFilter")
    Dim ws1 As Worksheet: Set ws1 = Sheets("One")
    Dim cAr As Variant, pAr As Variant, nRow As Long
    Dim Searchval, x As Integer
    Dim lr As Long: lr = ws1.Range("A" & Rows.Count).End(xlUp).Row
    
    Searchval = ws.[Y1].Value
    If Searchval = vbNullString Then Exit Sub
    
    cAr = Array("B2:B" & lr, "D2:D" & lr)
    pAr = Array("A", "F")
    nRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    On Error GoTo EndSub
    With ws1.[A1].CurrentRegion
        .AutoFilter 16, ">" & Searchval
        For x = LBound(cAr) To UBound(cAr)
            ws1.Range(cAr(x)).SpecialCells(xlCellTypeVisible).Copy
            ws.Range(pAr(x) & nRow).PasteSpecial xlPasteValues
        Next x
        ws1.AutoFilterMode = False
    End With
    
EndSub:
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

 

[lwarren30]

Didn't know I couldn't do. Google Drive

Actually you can't do that, it against Forum rule no 4 http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html.

Just upload the file to a site then put the link here.

 

[Kenneth Hobson]

I guess you wanted all of the filtered range so I added all of those fields in a criterion range.

 'CK2: ="=P-2"   'Exact Match
    'CK2: ="P-2"    'Partial Match
    Range("Sheet_K_2D_FinancialInput").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Range("CJ1:DG2"), _
        CopyToRange:=Range("AV5:BS5"), _
        Unique:=False

 

[Akuini]

In the criteria range don't use formula to get value from another range. So delete all formula in BV5:BZ5, and type P-2 to get values beginning with P_2 or type ="=P-2" to get exact match. And then run the code in post 3.

Here is an article that might be useful for you:
https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/

 

[lwarren30]

Thank you for the information. And it worked!

I was following this video and i've used this before in the past and it worked then. Why can't I use formulas in this situation?

 

[Kenneth Hobson]

It is odd. I would have to look at that youtube example more closely.

For now, if you want to do the criteria formulas and not refer to the data sheet's 1st row of data cells in it, I would use a null rather than "" as the null. It may look weird or not make sense. e.g. =IF(BV3="",,BV3)

Note the two commas, rather than "" between them.