fixing code to advanced filter for two columns together

[Ali M]

Hi
I try copying data from A:G if the columns C,F just contains values together . should ignore two cases if the column C,F contains zero together or columns D,E one of them contains values
this the code . currently copy whole data without exceptions

Sub arrange()
    Dim r As Range
    Sheets("sh").Cells(1).CurrentRegion.Clear
    With ActiveSheet
        Set r = .Cells.SpecialCells(4).EntireColumn.Range("G1:G2")
        r(2).Formula = "=AND(n(C2)<>0,n(F2)<>0)"
        .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Resize(, 7).AdvancedFilter 2, r, Sheets("sh").Cells(1)
        r.Clear
    End With
    With Sheets("sh").Cells(1).CurrentRegion
        If .Rows.Count > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).Columns(1) = Evaluate("row(1:" & .Rows.Count - 1 & ")")
        End If
    End With
End Sub
I  hope  finding  help

 

[Ali M]

can anybody see my request,please?

 

[Alex Blakenburg]

Are you able to get the Advanced Filter formula to work when you do it manually, and if so what is the full formula in your criteria cell ?
Your current formula does not cater for the or columns D,E one of them contains values. Is the test here for either of them being a numeric value and is 0 and acceptable value ?

 

[Ali M]

Hi Alex,

in reality there is no any formula in any cells . just using by code to do that .

yes should just search for numeric values for columns D,E with exclude cells contain 0 or empty cells for columns D,E

 

[Alex Blakenburg]

I am unclear of what your final criteria are. Ideally you create the formula in Excel first and try to filter it manually to prove the formula is correct before trying to use in in VBA.
Assuming the logic is that C AND F must have a non-zero value in it AND EITHER D OR E have a non-zero value in it, the formula below should work.

=AND(AND(N(C2)<>0,N(F2)<>0),OR(N(D2)<>0,N(E2)<>0))

I have used that in the code below.

Sub AdvancedFilterCopy()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim rngSrc As Range, rngDest As Range, rngCrit As Range
    Dim srcRowFirst As Long, srcRowLast As Long, srcColLast As Long
    Dim noOfCritCols As Long, noOfCritRows As Long
       
    Set srcSht = ActiveSheet                ' <--- Change this as required
    Set destSht = Worksheets("sh")          ' <--- Change this as required
    srcRowFirst = 1
    noOfCritCols = 1
    noOfCritRows = 2    ' Including Heading
        
    With srcSht
        srcRowLast = srcSht.Cells(Rows.Count, "G").End(xlUp).Row
        srcColLast = srcSht.Cells(srcRowFirst, Columns.Count).End(xlToLeft).Column
        Set rngSrc = .Range(.Cells(srcRowFirst, "A"), .Cells(srcRowLast, srcColLast))
        ' Temporary range for criteria
        .Columns(srcColLast + 2).Resize(, noOfCritCols).EntireColumn.Insert
        Set rngCrit = .Cells(srcRowFirst, srcColLast + 2).Resize(noOfCritRows, noOfCritCols)
    End With
    
    With destSht
        .Cells.Clear
        Set rngDest = .Range("A1")
    End With
    
    ' Set Criteria
    ' Column C criteria
    rngCrit.Cells(1, 1).Value = "Formula Criteria"                                      ' Optional temporary criteria headng
    rngCrit.Cells(2, 1).Value = "=AND(AND(N(C2)<>0,N(F2)<>0),OR(N(D2)<>0,N(E2)<>0))"    ' Filter formula
    
    ' Apply Filter and Copy to Destination
    rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
    rngCrit.EntireColumn.Delete
    
    With rngDest
        .CurrentRegion.EntireColumn.AutoFit
    End With

End Sub

 

[Ali M]

what does mean this error?

This command requires at least two rows of source data. You cannot use the command on a selection in only one row. Try the following:

- If you're using an advanced filter, select a range of cells that contains at least two rows of data. Then click the Advanced Filter command again.
- or you're creating a PivotTable, type a cell reference or select a range that includes at least two rows of data
in this line

 rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest

 

[Alex Blakenburg]

Without you providing an XL2BB of your data I am guessing the information required by the code.
The code assumes:
• The activesheet is the sheet to which you are going to apply the filter
• using xlup on Column G will give you the last row of the area to be filters
• the headings on sheet to be filtered are on row 1
• I haven't checked this but ideally row 1 has a column heading for Columns A to G and beyond if there are more columns.
• That your output sheet name is "Sh" and that the output is to A1

 

[Ali M]

The code assumes:
• The activesheet is the sheet to which you are going to apply the filter
• using xlup on Column G will give you the last row of the area to be filters
• the headings on sheet to be filtered are on row 1
• I haven't checked this but ideally row 1 has a column heading for Columns A to G and beyond if there are more columns.
• That your output sheet name is "Sh" and that the output is to A1

that's right

Without you providing an XL2BB of your data I am guessing the information required by the code.

ok
should be based on column C,F

asd.xlsx
	A	B	C	D	E	F	G
1	DATE	BRAND	STOCK	BUYING	SELLING	BALANCE	NOTE
2	1/7/2022	REF-BSTR-100	23	44	4	63
3	1/8/2022	REF-BSTR-101		55	1	54
4	1/9/2022	REF-BSTR-102	5			5
5	1/10/2022	REF-BSTR-103	7			7
6	1/11/2022	REF-BSTR-104	4	56		60
7	1/12/2022	REF-BSTR-105	57			57
8	1/13/2022	REF-BSTR-106	45		1	44
9	1/14/2022	REF-BSTR-107		55	4	51
10	1/15/2022	REF-BSTR-108		67	1	66
11	1/16/2022	REF-BSTR-109		45	2	43
12	1/17/2022	REF-BSTR-110	44			44
13	1/18/2022	REF-BSTR-111	77			77
14	1/19/2022	REF-BSTR-112	85			85
15	1/20/2022	REF-BSTR-113		76	6	70
16	1/21/2022	REF-BSTR-114	7			7
17	1/22/2022	REF-BSTR-115		7		7
main

result

asd.xlsx
	A	B	C	D	E	F	G
1	DATE	BRAND	STOCK	BUYING	SELLING	BALANCE	NOTE
2	1/7/2022	REF-BSTR-100	23	44	4	63
3	1/9/2022	REF-BSTR-102	5			5
4	1/10/2022	REF-BSTR-103	7			7
5	1/11/2022	REF-BSTR-104	4	56		60
6	1/12/2022	REF-BSTR-105	57			57
7	1/13/2022	REF-BSTR-106	45		1	44
8	1/17/2022	REF-BSTR-110	44			44
9	1/18/2022	REF-BSTR-111	77			77
10	1/19/2022	REF-BSTR-112	85			85
11	1/21/2022	REF-BSTR-114	7			7
SH

 

[Alex Blakenburg]

Thank you for providing the XL2BB data and expected results.

So ok a couple of changes.
1) Based on your XL2BB we can not rely on column G for your last row so change this from G to A:

    With srcSht
        srcRowLast = srcSht.Cells(Rows.Count, "G").End(xlUp).Row
        srcRowLast = srcSht.Cells(Rows.Count, "A").End(xlUp).Row

2) The Post 1 & 4 references to filtering on Column D & E appear to be irrelevant to change this line as well:

    rngCrit.Cells(2, 1).Value = "=AND(AND(N(C2)<>0,N(F2)<>0),OR(N(D2)<>0,N(E2)<>0))"    ' Filter formula
    rngCrit.Cells(2, 1).Value = "=AND(N(C2)<>0,N(F2)<>0)"    ' Filter formula

 

[Ali M]

perfect !
thanks for your assistance !