filter the same value for two columns doesn't work

[Maklil]

Hi
any help guys to fix this problem . I try to copy data based on column C or D contains value bigger than zero , then should just get rid of the data contains zero in column C,D together . my problem with this part it filters data columns C,D together when contains value bigger than zero ,but it supposes if one of them contains value bigger than zero, then should keep theses data and delete data contain zero in column C,D together .
is there any way by advanced filter or sorting whatever way guys ?

With ThisWorkbook.ActiveSheet.Cells(1).CurrentRegion
    
    .AutoFilter 3, "<>0"
    .AutoFilter 4, "<>0"
    .Copy Sheets(2).Cells(1)
    .AutoFilter
        End With

 

[jasonb75]

The simplest way to explain it is that the filter only shows rows where all columns meet the criteria. In your case that would be rows where columns c and d both contain a non zero value.

As far as I'm aware this is the same for all filters, simple or advanced. If you want it to show rows where only one column meets the criteria then you would need to add a helper column to identify the correct rows to show.

 

[Alex Blakenburg]

It is correct that Autofilter across columns is additive, but in advanced filter you can create an OR situation by putting the criteria across multiple rows.
The criteria across the columns in the same Row is AND but each row is OR to the other rows.

There are multiple ways of achieving the current requirement which as I understand it is to copy the rows where either Column C OR D are not zero.
We could use advanced filter to target just the rows to be kept but we could also copy all rows to start with and then reverse the current filter to be where C = 0 and D = 0 and delete those rows from the copy.
Of course using a helper column or an array are also options.

For us to write code for an advanced filter we would need an XL2BB of the data (we will need the names of column C & D and the name of the sheet to copy to and whether we need to clear that sheet before copying to it.

 

[Maklil]

thanks guys !
here is the sample but my real data could be 2000 rows

(4).xlsm
	A	B	C	D	E
1	ITEM	BRAND	PURCHASE	SALES	BALANCE
2	1	ref-air1000	11		110
3	2	ref-air1001		10	220
4	3	ref-air1002			300
5	4	ref-air1003			100
6	5	ref-air1004	11		110
7	6	ref-air1005			200
sheet1

output after filter

(4).xlsm
	A	B	C	D	E
1	ITEM	BRAND	PURCHASE	SALES	BALANCE
2	1	ref-air1000	11		110
3	2	ref-air1001		10	220
4	3	ref-air1004	11		110
sheet2

For us to write code for an advanced filter we would need an XL2BB of the data (we will need the names of column C & D and the name of the sheet to copy to and whether we need to clear that sheet before copying to it.

yes should clear that sheet before copying to it.

 

[Alex Blakenburg]

See if this does what you need.

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 = Worksheets("Sheet1")
    Set destSht = Worksheets("Sheet2")
    srcRowFirst = 1
    noOfCritCols = 4    ' Criteria on 2 column and for each <>0 and <>""
    noOfCritRows = 3    ' Including Heading (the column criteria are OR)
    
        
    With srcSht
        srcRowLast = srcSht.Cells(Rows.Count, "A").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 = srcSht.Range("C" & srcRowFirst).Value
    rngCrit.Cells(2, 1).Value = "<>0"
    rngCrit.Cells(1, 3).Value = srcSht.Range("C" & srcRowFirst).Value
    rngCrit.Cells(2, 3).Value = "<>"
    ' Column D criteria
    rngCrit.Cells(1, 2).Value = srcSht.Range("D" & srcRowFirst).Value
    rngCrit.Cells(3, 2).Value = "<>0"
    rngCrit.Cells(1, 4).Value = srcSht.Range("D" & srcRowFirst).Value
    rngCrit.Cells(3, 4).Value = "<>"
    
    ' Apply Filter and Copy to Destination
    rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
    rngCrit.EntireColumn.Delete
    
    With rngDest
        .CurrentRegion.EntireColumn.AutoFit
    End With

End Sub

 

[Maklil]

great !
this is exactly what I want ,but remains one thing. I would after filter in sheet 2 should re-auto number in column A

 

[Alex Blakenburg]

OK replace this at the bottom:

    With rngDest
        .CurrentRegion.EntireColumn.AutoFit
    End With

With this

    With rngDest.CurrentRegion
        .EntireColumn.AutoFit
        ' Renumber Items
        .Columns(1).Cells(2, 1).Value = 1
        .Columns(1).Offset(1).Resize(.Columns(1).Rows.Count - 1).DataSeries _
                    Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
                    Step:=1, Trend:=False
    End With

 

[Maklil]

excellent ! all of things are great as I expected .
much appreciated for your assistance .

 

[Alex Blakenburg]

Thanks for the feedback. Glad we could help.