Autofilter and delete all values except #N/A

[Veeru106]

Hi,

Please suggest a code which can enable me to autofilter and delete all values except #N/A.

Like i have values as below

1
2
6
3
#N/A
#N/A
#N/A

I want a code which will filter all values except #N/A and delete them.

Remaining values will be #N/A only

Thanks in advance

 

[AlanSidman]

This assumes your data is in Col A

Option Explicit

Sub delNum()
Dim i As Long
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For i = lr To 1 Step -1
If IsNumeric(Range("A" & i)) Then Range("A" & i).Delete
Next i
End Sub

 

[Veeru106]

Not sure but this is not working.

attaching my sheet herewith for your review
Thanks

 

[Monty]

Option Explicit

Sub delNum()
Dim i As Long
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For i = lr To 1 Step -1
If  Range("A" & i)<>" N/A" Then Range("A" & i).Delete
Next i
End Sub

Try this

 

[Monty]

Option Explicit

Sub delNum()
Dim i As Long
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For i = lr To 1 Step -1
Application.DisplayAlerts= False
If  Range("A" & i)<>" N/A" Then Range("A" & i).Delete
Application.DisplayAlerts=True
Next i
End Sub

Small change...

 

[Veeru106]

Still not working....showing type mismatch...

 

[Monty]

Option Explicit

Sub delNum()
Dim i As Long
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For i = lr To 1 Step -1
Application.DisplayAlerts= False
If  Range("A" & i)<>" N/A" Then Range("A" & i).Delete
Application.DisplayAlerts=True
Next i
End Sub

Hello Veeru...am very active member on this forum...but unfortunately my desktop is shut and trying to answer from mobile...we are almost there with the code...try recoding macro with autofilter and N/A option ...and same try to replicate.

 

[Veeru106]

I tried recording...though on current sheet it is working but in real world i have N numbers of items...it means if i go with below code then i need to manaully enter each item like Ram, Sham & so on...Below is the code i have

Sub vvvvvv()
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$F$17").AutoFilter Field:=1, Criteria1:=Array( _
"Lalit", "Meenu", "Monu", "Rahul", "Ram", "Sham", "Sonu"), Operator:=xlFilterValues
Rows("2:8").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter
End Sub

 

[Monty]

In criteria now mention N/A and run the code to filter data.

 

[AlanSidman]

I have tested my code and it works for me. Did you try my code in the second post?

It appears that you have changed up your requirements. How about uploading a sample worksheet and tell us what you really want. With your current method of telling what you want and have, it is akin to throwing darts blindfolded for us.

 

[AlanSidman]

Revised code based upon your worksheet

Sub Delete()
Dim i As Long
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

For i = lr To 2 Step -1
If Not IsError(Range("A" & i)) Then Range("A" & i).Delete
Next i
End Sub

If you wish to delete the whole line instead of just the cell, then add this to just before the .Delete

.EntireRow

 

[vletm]

Veeru106
If You really need to use AutoFilter then test this...

 

[Veeru106]

Above code is working for me now

Sub Delete()
Dim i AsLong
Dim lr AsLong
lr = Range("A" & Rows.Count).End(xlUp).Row

For i = lr To 2 Step -1
IfNot IsError(Range("A" & i)) Then Range("A" & i).Delete
Next i
EndSub

..but code you write in Veeru106 sheet is different...and it has 2 codes..i have some queries:-
1. How this is different from above one
2. which code is assigned to Do It button in Veeru106 File

thanks

 

[vletm]

Veeru106
You asked code which used AutoFilter - okay?
then You got codes without AutoFilter - okay?
You can see both versions in that code - okay?
My above code is without AutoFilter - which You didn't ask and
my below code is with AutoFilter - which You asked.

Your named code with #13 is basic same.
My code deletes only column's from A to F -values and
if You have a lot of rows to check then this would be quicker.
... but it isn't what You ask!

The below code is Assign with [ Do It ]-button ( with AutoFilter ).

The results are same with or without AutoFilter!

 

[Veeru106]

ok got it thanks