Filter data set to #N/A, skip step entirely if none

[rachel06]

Hi all,

I know several threads exist on this topic but I'm apparently really bad at wording my searches because I'm not finding my answer.

Below is the code I'm working with for my macro for a specific data set. It filters to #N/A lines, moves them to a new sheet, then deletes them from the original sheet.

I need to make two changes to it.

1. Not have exact cell references (pertaining to the # of rows - the A-D will always be the same.
2. Skip this step entirely if there are no #n/a values present. Right now, the macro bugs out if there are no #n/a lines

Thanks so much!!

  ActiveSheet.Range("$A$1:$D$7116").AutoFilter Field:=4, Criteria1:="#N/A"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("NA Lines").Select
    Range("A1").Select
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
    Sheets("MACRO").Select
       lr = Cells(Rows.Count, 1).End(xlUp).Row
    If lr > 1 Then
        Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
    ActiveSheet.ShowAllData

 

[RoryA]

Try this:

With ActiveSheet.Range("A1").CurrentRegion
   .AutoFilter Field:=4, Criteria1:="#N/A"
   If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
      .Copy Sheets("NA Lines").Range("A1")
      Sheets("NA Lines").UsedRange.EntireColumn.AutoFit
      .Resize(.Rows.Count - 1).Offset(1).Columns(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
   End With
    ActiveSheet.ShowAllData

 

[rachel06]

Try this:

With ActiveSheet.Range("A1").CurrentRegion
   .AutoFilter Field:=4, Criteria1:="#N/A"
   If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
      .Copy Sheets("NA Lines").Range("A1")
      Sheets("NA Lines").UsedRange.EntireColumn.AutoFit
      .Resize(.Rows.Count - 1).Offset(1).Columns(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
   End With
    ActiveSheet.ShowAllData

Perfection. It even runs faster. Thanks so much!!!!

 

[RoryA]

Glad we could help.