Free Microsoft Excel 2013 Quick Reference

Delete Cells With #N/A!

Hi all

I have used Vlookup to search and to return specific values to column 77. For those values that are not defined, Vlookup will return #N/A (null value). Can I know how to write a macro that can go through each cell in column 77 and delete those cells that have null value? I tried the following code but it doesn't work

    Dim rwindex, colindex 
    colindex = 77 
    For rwindex = 658 To 663 
        With Worksheets("Invoice").Cells(rwindex, colindex) 
            If .Value = Null Then 
                Worksheets("invoice").Cells(rwindex, colindex).ClearContents 
            End If 
        End With 
    Next rwindex 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Post your answer or comment

comments powered by Disqus
What formula will eliminate the cell with #N/A when summing?


Can help with copying and pasting cells that contain only numbers ignoring the cells with #N/A values?
How to use the LINEST function if the data contains #N/A values?


I need some help developing a macro that will test a cell. If the cell displays "#N/A" then I want the row deleted. Then move on to the next cell.

I have a MATCH formula that will return #N/A when data is not found. I need a macro to delete all rows with #N/A in a specific column. Thanks to anyone who can help.

I have a row with a formula that returns #N/A and the rest return numbers. How can I sum this row with a formula that will exclude the cell with #N/A? Please see attachment. Your help is greatly appreciated!

- - - Scott

Hello all,

My problem centers around #N/A N/A. I need to calculate the mean and the STDEV of a cellrange. The cells in this range are being updated, linked to Bloomberg. For some companies, Bloomberg will not have the information and #N/A N/A will be shown. However, if in the range, one of the cells has #N/A N/A, the mean and STDEV can't be calculated.

This is the current formula: Code:
Does anyone know how to change the formula in order to skip these #N/A N/A cells?

Kind regards,


I have a cell that returns a value #N/A if information is not available,
I need to do an If statement to see if this cell has #N/A in it, but if
I do =IF(A1="#N/A","TRUE","FALSE") it doesn't work. Any ideas?

ChrisMattock's Profile:
View this thread:

01/01/04 36.83
02/01/04 38.93 How can I fill in these spaces with #N/A in col. C
03/01/04 37.78 without erasing the cels that have the average.
04/01/04 39.96 So I can make the graph.
05/01/04 35.35
06/01/04 34.82
07/01/04 38.93 37.51
08/01/04 30.31 Undernead is the formula to calc
09/01/04 35.64 the average of the week
10/01/04 34.57
11/01/04 38.14 =SI(JOURSEM(A11)=4;MOYENNE(B5:B11);"")
12/01/04 37.88
13/01/04 34.49
14/01/04 38.27 35.61
15/01/04 35.89
16/01/04 34.11
17/01/04 34.25
18/01/04 36.64
19/01/04 34.91
20/01/04 35.48
21/01/04 36.83 35.44
22/01/04 33.41
23/01/04 33.24
24/01/04 37.57
25/01/04 38.42
26/01/04 23.29
27/01/04 29.44

I have a cell that returns a value #N/A if information is not available, I need to do an If statement to see if this cell has #N/A in it, but if I do =IF(A1="#N/A","TRUE","FALSE") it doesn't work. Any ideas?

I am currently running formulas IF(COUNTBLANK) to self fill other cells corresponding to dates in another cell. However, what I want to determine is how to take the column of data that has some cells filled with N/A where other are filled with dates, but I don't want the cell that the formula is in to change to a Y until all the empty cells (those not N/A) have a date in it.

This is my current formula:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In the referenced cells, 17 have N/A but two will be populated with dates, I don't want the "Y" to show up until both cells have dates.

Hi Excel Gurus,

Is there any way to sum a column of cells with "#N/A" appearing in half of them. For example, I have good data in the rows 1-5 of column P, then 2 rows of "#NA" (6 and 7), then 5 more rows of good data. This data is dynamic, therefore would not be the same each time. Is there a solve for summing data in the good cells, but skipping those with "#N/A" as a value?

Fingers crossed...

- Excel Novice

Hello, I am trying to sum these cells with some of them having N/A. The cells that I am trying to add are:


Thanks for the help!

I have a sheet with 2884 rows.. I am currently using the following macro (with changes for each column) to search columns AN - BG and delete cells with a value of 0 and move the columns that do not = 0 up..

for example:


would be


I can not delete the entire row, because the next column may not have a 0 value in the same row..

Obviously, the process I have below takes FOREVER. Is there a faster way to do this? Again, it is Column AN through Column BG and rows 6-2884 . I am going through it backwards so that I do not miss any 0's.

Dim ANcell As
Dim ANCount As Long
  For ANCount = 2884 To 6 Step -1
  'Start from bottom of range, so we don't skip any rows When we've deleted one
  If Trim(Range("AN" & ANCount).Value) = "0" Then
     Range("AN" & ANCount).Delete Shift:=xlUp
  End If
  Next ANCount

I am creating a blank billing sheet and I have got a couple of different formulas in there. I am trying to use a sum formula at the bottum to sum the totals of labor and equipment. The sum won't total if there are cells with the #n/a. What I've got is a VLOOKUP formula to pull up the rate and then a HRS Worked column. The rate and HRS Worked multiply to give a labor total for one person. But, If there is no classifiacation the VLOOKUP cannot bring up a rate and therefore cannot put a labor total in for that person. I have a labor total at the bottum for all of the employers. That will not total if there is one cell with out a total for one employee. If there is not a total for an employee it will show up as #n/a because the formula cannot total for that employee.

I would think that there is a way that you can sum the column even if there is a #n/a cell in the column.

Any help is greatly appreciated.


Say I have cells A1=3, A2=3, A3=N/A, A4=5, A5=1. Now I want to put an equation in cell B1 and I want to add A1+A2+A3+A4+A5 and have it come up with 12 instead of an error. Can you please help me.

Thank you

Deleting Cell with formulas with Numbers Ex. =2+3 not = A+B

I am trying to create a copy of workbook with only formulas, but some cells
exist wit
=2+3 which I do Not wanted. How can I delete all of this in a single click
(Not Individualy)

I have tried search but can't find the answer to this.

I have 18 months figures down three thousand rows and across 12 rows. I want to use the Max function to pick out the month with the highest number from each row. The problem is that the data is collated from .csv files that drops off a month every time one is produced.

i.e. The July file contained data back to April 2004 the August file data back to May 2004.

This means I have #N/A in some cells how can I max a row with #N/A in it?

Thanks in advance.

Does someone know of a way to delete cells with a zero in them, but not delete cells that are blank with vba code?


I have some cells in a worksheet with #N/A. These cells have the formula =N/A() so that they won't appear in the chart. I need to write a "if function" in another cell like this = if(cell1 = "#N/A", "good","bad"). This is not working .... what is the error here?

hi im currently trying to sum data on a sheet, however some of the data i am trying to sum is #N/A under the current conditions.

i would like to know if there is an if statement i can use to make the cells with #N/A become 0 so i can sum the data.

thanks ash.

Hi All,

I have a spreadsheet with an Array entered Formula (based on Rows using the
TRANSPOSE Function) that produces the #N/A error when no further data is
available to be calculated: this in itself is fine - the #N/A is disguised
with Conditional Formatting. However, if possible, I would like to find the
AVERAGE of the values in each Row excluding those cells with #N/A from the

I've tried a few variations using either the ISERROR or ISNA Function nested
with the AVERAGE Function. I get either zero or #N/A returned.

The original Formulae providing the results that I now wish to Average was
brilliantly created by Dominic in this previous Post: "Count Intervals of
Filtered TEXT values in Column and Return Count across a Row"

The values returned from the above Formulae is numeric and formatted as


Message posted via

On the attached example i have list of values in column A. What i want is a macro that runs through each cell in the column and deletes the any cell with #N/A in it.

I tried the following code in a macro but got an error when i tried to run it.

    For Each cell In Selection
    If cell.Value = "#N/A" Then
        cell.Value = ""
    ElseIf cell.Value = "#N/A" Then
        cell.Value = ""
    End If

I need to fill a column with n/a for all records as sometimes there are blank cells.
Can i populate this column say K with "n/a" as Column A will have no blank cells in the column as a reference.


I'm using paste/special to remove vlookup functions which leave #N/A when no matches were found. I'm not getting any errors but I can't get the current cell to clear if it has the #N/A. Can anyone assist? Thanks in advance!

For i = 7 To lastRow
If (CStr(Cells(i, "L").Value) = "#N/A") Then
Cells(i, "L").Value.Clear
End If
Next i

No luck finding an answer? You could always try Google.