PDA

View Full Version : [SOLVED] How can I fix my autofilter if criteria not met?



roxnoxsox
12-08-2015, 08:54 AM
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

Aflatoon
12-08-2015, 09:22 AM
What exactly do you mean by "getting stuck"?

Also, are your headers in row 1 or 2?

snb
12-08-2015, 09:27 AM
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

roxnoxsox
12-08-2015, 09:28 AM
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!

roxnoxsox
12-09-2015, 02:24 AM
snb - sorry, how/where would I put this in my macro?

EDIT: I can see how this works now, many thanks!