Auto Filter

[nianchi111]

Hi All,

I have a data in a sheet

Need to filter column K..

If #N/A then move the filtered data to next sheet.
Leave other data in sheet 1.

Important thing is that if "#N/A" not found then do nothing.

Thanks,
Vimal Vikraman.

 

[Peter_SSs]

.. move the filtered data to next sheet.

Does the "next sheet" already exist?
If so ..
- What is its name?
- Does it (or might it) already have data on it? If so, what happens to that existing data? (eg It should get deleted or the new data should get added below etc)

 

[nianchi111]

Does the "next sheet" already exist? = Yes
- What is its name? = Sheet 2
- Does it (or might it) already have data on it? = No data only the header

 

[Peter_SSs]

Does the "next sheet" already exist? = Yes
- What is its name? = Sheet 2
- Does it (or might it) already have data on it? = No data only the header

Try this with a copy of your workbook.

Sub Move_NA()
  Dim rCrit As Range
  
  With Sheets("Sheet 1")
    Set rCrit = .Range("AA1:AA2")
    rCrit.Cells(2).Formula = "=ISNA(K2)"
    With .Range("A1").CurrentRegion
      .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
      .Offset(1).Copy Destination:=Sheets("Sheet 2").Range("A2")
      On Error Resume Next
      .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).EntireRow.Delete
      rCrit.ClearContents
      On Error GoTo 0
    End With
    If .FilterMode Then .ShowAllData
  End With
End Sub