Can anyone correct my 3liner of codes?

[edlim85]

im getting a error here..pls help

As the code, can tell what i want to do is go through each cells in my used range. clearcontent for cells containing "NA" or "#N/A"

For Each cell In w1.UsedRange.cells
If cell.Value = "NA" Or cell.Value = "#N/A" Then cell.ClearContents
Next cell

Regards
Edmund

 

[edlim85]

<TABLE border=0 cellSpacing=0 cellPadding=6 width="100%"><TBODY><TR><TD style="BORDER-BOTTOM: 1px inset; BORDER-LEFT: 1px inset; BORDER-TOP: 1px inset; BORDER-RIGHT: 1px inset" class=alt2>If CStr(cell.Value) = "NA" Or CStr(cell.Value) = CStr(CVErr(xlErrNA)) Then cell.ClearContents </TD></TR></TBODY></TABLE>
Hmm...*strangely to me* all my repeated values are deleted as well..
i wonder if it is because of this line

If CStr(cell.Value) = "NA" Or CStr(cell.Value) = CStr(CVErr(xlErrNA)) Then cell.ClearContents

hi Peter_SSs !,

my range varies. max 36rows and 27columns


Can you advise me code shld i go with?

Regards
Edmund

 

[Peter_SSs]

hi Peter_SSs !,

my range varies. max 36rows and 27columns


Can you advise me code shld i go with?

Regards
Edmund

Did you try the code I already suggested?

 

[edlim85]

Hi Peter_SSs,

I did. it works for "NA" but didnt clear "#N/A"

With w1.UsedRange
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
On Error GoTo 0
.Replace What:="NA", Replacement:="", LookAt:=xlWhole, _
Searchformat:=False, ReplaceFormat:=False
End With

thanks

 

[Peter_SSs]

.. didnt clear "#N/A"

Didn't you indicate that those cells contain formulas that produce the #N/A errors?

 

[edlim85]

My apology. I shld have make myself clear at the beginning. I doing a macro to rearrange data in a specific way. i have a line of coding in front that automatically return #N/A to cells if the row length(RowLenS) is more than the length of transposed range.

w1.cells(NR, 19).Resize(, RowLenS) = Application.Transpose(w1.Range(w1.cells(4, 8), w1.cells(LR, 8)).Value)

After rearranging all my data into required. I intend to clear content of cells containing "#N/A" and "NA". Where "NA" is one of the raw data.

hope this is clear.

 

[edlim85]

oh dear, the 1st few times i run it gives me Error 2042.

can i use this instead? it seem to work

With w1.UsedRange
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
On Error GoTo 0
.Replace What:="NA", Replacement:="", LookAt:=xlWhole, _
Searchformat:=False, ReplaceFormat:=False
.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
Searchformat:=False, ReplaceFormat:=False
End With

 

[Peter_SSs]

oh dear, the 1st few times i run it gives me Error 2042.

With w1.UsedRange
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
On Error GoTo 0
.Replace What:="NA", Replacement:="", LookAt:=xlWhole, _
Searchformat:=False, ReplaceFormat:=False
.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
Searchformat:=False, ReplaceFormat:=False
End With

can i use this instead? it seem to work

If it works, you can use it.

However, from what I can gather, you can probably leave out the red bit and it will still work. Try that.

 

[edlim85]

Big Thank U, Peter_SSs.


Regards
Edmund