Filter

[Rafael Alvares Tubero]

Hello, everybody!

Guys, I was trying to write a code that would make a filter on some data, and then it would cut those data selected by the filter and paste in another sheet. But I would like that the code was able to just cut and paste only what was selected, and what is happening with my actual code is: it is cutting and pasting everything, not just what was selected... check part of the code out...

ActiveSheet.Range(Cells(1, 1), Cells(ult_lin, ult_col)).AutoFilter Field:=7, Criteria1:=">" & Date - 1, _
Operator:=xlAnd
Cells(2, 2).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Cut
Sheets("NDF_termo").Select
ult_lin = Cells(Rows.Count, 2).End(xlUp).Row
Cells(ult_lin + 1, 1).Select
ActiveSheet.Paste

can you guys help me, please???

thanks a lot!

 

[Sektor]

Sub F()

    Dim lastRow As Long
    
    lastRow = Range("A1").End(xlDown).Row
    
    ActiveSheet.Range(Cells(1, 1), Cells(ult_lin, ult_col)).AutoFilter Field:=7, Criteria1:=">" & Date - 1, Operator:=xlAnd
    
    Range("A2", Cells(ult_lin, ult_col)).SpecialCells(xlCellTypeVisible).Cut
   
    Sheets("NDF_termo").Select
    ult_lin = Cells(Rows.Count, 2).End(xlUp).Row
    Cells(ult_lin + 1, 1).Select
    ActiveSheet.Paste

End Sub

 

[njimack]

Something like this (untested)

ActiveSheet.Range(Cells(1, 1), Cells(ult_lin, ult_col)).AutoFilter Field:=7, Criteria1:=">" & Date - 1, _
Operator:=xlAnd

Range(Cells(1, 1), Cells(ult_lin, ult_col)).SpecialCells(xlCellTypeVisible).EntireRow.Cut

With Sheets("NDF_termo")
    ult_lin = .Cells(Rows.Count, 2).End(xlUp).Row
    .Cells(ult_lin + 1, 1).Paste
End With

 

[Rafael Alvares Tubero]

Thanks everybody!

 

[Rafael Alvares Tubero]

Guys,

You know what I have noticed... when I apply this filter and try to cut all it is visible and there is no data after the filter, of course, it doesn't work. And what happen is: or it cuts the headline when it supposed to do not cut, or it just give me a error where a error treatment just doesn't work in this case.

Could you help me, once again?


Sincerely,

 

[Sektor]

You must assign filtered range to Range variable and check it for Nothing. If it ain't Nothing, then you have some data and can cut.

Sub F()

    Dim lastRow As Long, rngCopy As Range
    
    lastRow = Range("A1").End(xlDown).Row
    
    ActiveSheet.Range(Cells(1, 1), Cells(ult_lin, ult_col)).AutoFilter Field:=7, Criteria1:=">" & Date - 1, Operator:=xlAnd
    
    Set rngCopy = Range("A2", Cells(ult_lin, ult_col)).SpecialCells(xlCellTypeVisible)
   
    If Not rngCopy Is Nothing Then
        rngCopy.Cut
        Sheets("NDF_termo").Select
        ult_lin = Cells(Rows.Count, 2).End(xlUp).Row
        Cells(ult_lin + 1, 1).Select
        ActiveSheet.Paste
    End If

End Sub

 

[Rafael Alvares Tubero]

Right, but when I set rngcopy and I give it the range to cut, that give me a error because I cannot select many ranges to cut, got it!?

It would work just for one line or if the lines was straighted, got it!?

any hint!?