VBA help

[Donai]

Hi, I have the below code that filters data and then copies to another sheet. I need this code to be fine tuned, to allow flexibilty with the range with autofilter plus i would like the code to delete the filtered data once it has copied to another sheet

Sub Macro2()
Dim rng As Range
Dim rng2 As Range
Application.ScreenUpdating = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$7:$G$745").AutoFilter Field:=2, Criteria1:="=*pi*", _
    Operator:=xlAnd
With ActiveSheet.AutoFilter.Range
 On Error Resume Next
   Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
       .SpecialCells(xlCellTypeVisible)
 On Error GoTo 0
End With
If rng2 Is Nothing Then
   MsgBox "No data to copy"
Else
   Worksheets("NE").Cells.Clear
   Set rng = ActiveSheet.AutoFilter.Range
   rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
     Destination:=Worksheets("NE").Range("A8")
End If
   ActiveSheet.ShowAllData
Application.ScreenUpdating = True
'
End Sub

 

[Weaver]

you could use

rng2.entireRow.delete

to delete the filtered data from the filter range

hth