Multiple cells with multiple criteria

[vangoori]

Hi All,

i am looking for VBA code for below mentioned criteria

Column "A1" to column "E1" i have data ... i need find out the

"#N/A" and "Zero" in that list and delete.

A	B	C	D	E
1	#N/A	251	0	45
2	3	0	#N/A	245
2	2	23	123	0
2	5	245	125	2
#N/A	5	2	45	24
0	3	#N/A	24	#N/A

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

Thanks,
Vangoori.

 

[vangoori]

Hi All,

i am able to write code for each and every columns...(seprate code each column)....


Sub multiplefiltercriteria()


Range("a1").Select
ActiveSheet.Range("$A$1:$E$7").AutoFilter Field:=1, Criteria1:="=0", _
Operator:=xlOr, Criteria2:="=#N/A"
Selection.ClearContents

Range("B1").Select
ActiveSheet.Range("$A$1:$E$7").AutoFilter Field:=1, Criteria1:="=0", _
Operator:=xlOr, Criteria2:="=#N/A"
Selection.ClearContents



End Sub


till Column E ...but i don't want to write code for each column....can any help me on this.... ?


Thanks,
Vangoori.

 

[venkat1926]

try this. data in sheet1. copy sheet1 to sheet2 before running macro so that the old data is available if something goes wrong.

Sub test()
Dim r As Range, c As Range
Worksheets("sheet1").Activate
Set r = Range("A1").CurrentRegion
For Each c In r
If IsError(c) Then c = ""
If c = 0 Then c = ""
Next c


End Sub

 

[vangoori]

Hi Venkat,

first of all ..i would like to thanks for your time .....code is working perfect.

if possible could please explain me how the code is working ,,

Thanks for your help.

Vangoori.

 

[venkat1926]

It loops through each cell of the data and if it is error(#N/A) it is changed to blank
similarly if it is 0. I tried using "replace" method it did not work.