Consulting

Results 1 to 5 of 5

Thread: How can I fix my autofilter if criteria not met?

  1. #1

    How can I fix my autofilter if criteria not met?

    I've seen a couple of similar threads but I'm struggling to follow how the problem was fixed..

    I'm trying to set up a macro which will autofilter my data based on column B and delete any which say: #N/A

    However, sometimes my data doesn't contain: #N/A at all and I'm GUESSING this is where it's getting stuck? See as below:

        Rows("1:1").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
        Rows("1:1").Select
        Selection.AutoFilter
        ActiveSheet.Range("B2:B" & LastRow).AutoFilter Field:=2, Criteria1:="#N/A"
    With ActiveSheet.AutoFilter.Range
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    End With
        ActiveSheet.AutoFilterMode = False
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
    Thanks in advance and sorry for being a doofus

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    What exactly do you mean by "getting stuck"?

    Also, are your headers in row 1 or 2?
    Be as you wish to seem

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Did you consider using ?

    Sub M_snb()
        on error resume next
        Columns(2).SpecialCells(-4123, 16).EntireRow.Delete
        Columns(2).SpecialCells(2, 16).EntireRow.Delete
    End Sub

  4. #4
    Sorry, when I saying getting stuck - I mean I end up with no data on my spreadsheet whatsoever - it's just deleted everything rather than checking for: #N/A and deleting only those rows.

    I don't have any headers in my original data which is why I'm trying to insert a line above row 1 to use.

    Sorry if this sounds stupid, thank you!

  5. #5
    snb - sorry, how/where would I put this in my macro?

    EDIT: I can see how this works now, many thanks!
    Last edited by roxnoxsox; 12-09-2015 at 04:43 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •