Error in removing #N/A from excel column

Help Activities
, , ,
1

I have to remove the rows which has #N/A on column “AM” -. which is vlookup values. When I read the data and use filter data row activity and then writing down the data it gives below output and some different values of #N/A. Can someone suggest solution for this ? I tried using Formula “Read_LineItems.AsEnumerable().Where(Function(row)Not (row(“AM”).ToString.Trim.Contains(”#N/A"))).CopyToDataTable

This formula also gives same results.

image
image124×506 2.36 KB

image

2

@Pal_Patel

# NA would come when there is some error in formula

Vetter way to handle is to modify excel formula…to display blank instead of #NA using iferror formula in excel

If that is not feasible…then. First try with find/replace activity and check if it works

If that also does not work…then after the change in datatable are you writing the data back to excel?

Cheers

1 Like
3

Yes, I am writing the data back in excel. Sending you sample file.
Book1.xlsx (28.3 KB)

How find and replace work ? I want to remove #N/A rows.

4

@Pal_Patel

Just use find/replce and then find for na and select replace all and replace with blank

Cheers

5

ok let me try that . Thanks

1 Like
6

Can you check here if I made any mistake ? because this makes no changes on sheet.

image
image369×684 46.5 KB

7

@Pal_Patel,

For this, you can use Invoke VBA.

VBA Code to filter and delete #N/A rows.

Sub RemoveNA()
    Dim lRow As Long, Rng As Range
    lRow = With Sheets(1)
    	   	.Range("A" & .Rows.Count).End(xlUp).Row
	   End With 
    ThisWorkbook.Sheets(1).Activate
    Range("B1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$D$" & lRow).AutoFilter Field:=2, Criteria1:="#N/A"
    Set Rng = Range("A1:D" & lRow).SpecialCells(xlCellTypeVisible)
    If Rng.Cells.Count > 1 Then
        Range("A1:D" & lRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Else
    
    End If
    Selection.AutoFilter
End Sub

image
image586×769 33.2 KB

Thanks,
Ashok :slight_smile:

1 Like
8

#N/A is not a value. It’s an error message in Excel. The best thing to do would be to correct the formula in Excel so you get a proper value instead of an error.

9

Can someone help on this. This is not working for me.

10

Can you help me, this is not working for me.

11

Why don’t you just correct the formula in Excel so that it doesn’t generate the #N/A error?

12

the task is that if there is #N/A then just remove it. How should I correct the formula ?

13

Show us the formula you have in those cells.

14

=VLOOKUP(AL2,AN:AO,2,FALSE)

I also attached
Book1.xlsx (28.3 KB)
sample file.

15

It’s because the lookup doesn’t find a match. Try this.

=IFNA(VLOOKUP(A2,C:D,2,FALSE),"Not found")

16

This gives an error. Can you share sample file ?

Write Cell: The write operation failed, because one of the following reasons:
► The data you want to write “=IFERROR(VLOOKUP(AL4,AN:AO,2,FALSE),‘NotFound’)” has a wrong format;
► Excel is busy or you are currently editing a cell;
► If your data is a formula, make sure you use comma as parameters separator;
► During the write operation make sure

17

You don’t use single quotes in Excel, you use double quotes.

What are you trying to do? Why are you writing that vlookup to Excel using UiPath? Why don’t you just do the lookups in your automation and then write the data?

18

If you have any solution just sent me. I am doing what I have in my task to do.I am using single quotes in vlookup formula.

19

You don’t use single quotes. You use double quotes.

And you’re supposed to use IFNA not IFERROR.

I am doing what I have in my task to do

Wouldn’t you like to find out if there’s a better way to do what you’re trying to achieve?

20

IfNA used . same error coming. Let me know any other solution if you have. I want to remove that N/A rows from the sheet.