Solved can I sort a list containg NA in another column using macro?

July 11, 2012 at 04:47:05
Specs: Windows Vista
I have a list in column A that contains lots of NA (eg RED, BLUE, NA, NA,BLACK, NA,NA, GREEN, etc.)
I would like a new list containg all values except NA of list in column A in column B.
Can I do this with or without macro? I m using Excel 2003
Please help me out......

See More: can I sort a list containg NA in another column using macro?

Report •

✔ Best Answer
July 18, 2012 at 06:29:14
Well, you could add cells to the column as long as you add them to all of the columns that the VLOOKUP refers to. The VLOOKUP will automatically update the table_array argument to match the new location.

However, if you want to use a macro, this will take a list in Column A and put it in Column B without the NA.

Sub NoNA()
'Determine last used row in Column A
 lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows in Column A
   For rw = 1 To lastRw
'If value is not NA, increment Row counter for
'Column B and copy the value
    If Cells(rw, 1) <> "NA" Then
      nxtRw = nxtRw + 1
      Cells(nxtRw, 2) = Cells(rw, 1)
    End If
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 11, 2012 at 06:47:16
If the NA is a result of an error, it should be #N/A.

You can try this: =IF(ISNA(A1),"",A1)

Drag down as many rows as needed.

If the NA is actually the TEXT letters N and A

You can try this: =IF(A1="NA","",A1)

Drag down as many rows as needed.

MIKE

http://www.skeptic.com/


Report •

#2
July 11, 2012 at 07:22:44

Report •

#3
July 16, 2012 at 03:51:35
NA is entered as Text

Report •

Related Solutions

#4
July 16, 2012 at 06:58:55
You could use Advanced Filter with Criteria. Review the Help files for Advanced Filter for a complete explanation.

Let's say you have this:

       A
1   MyList
2     Red
3     Blue
4     NA
5     Black
6     NA
7     NA
8     Green
9     NA
10    White

Insert 3 rows above your list and add the 2 new pieces of data as shown below:

       A
1    MyList
2    ="<>NA" (Note: This will display <>NA)
3      
4    MyList
5     Red
6     Blue
7     NA
8     Black
9     NA
10    NA
11    Green
12    NA
13    White

1 - Click: Data...Filter...Advanced Filter
2 - Select: Copy to another location
3 - List range: $A$4:$A$13
4 - Criteria range: $A$1:$A$2
5 - Copy To: $B$4
6 - OK

If the list changes, you'll need to redo the filter.

You could use a macro to update the filter automatically, but since you don't want to use a macro to create the list, I assume that you don't want a macro to update the filter either.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
July 18, 2012 at 04:23:42
I can't add any cell in the list as I am using this column in Vlookup
& I am ready to do all this using Macro. Please Debry help me out of this

Report •

#6
July 18, 2012 at 06:29:14
✔ Best Answer
Well, you could add cells to the column as long as you add them to all of the columns that the VLOOKUP refers to. The VLOOKUP will automatically update the table_array argument to match the new location.

However, if you want to use a macro, this will take a list in Column A and put it in Column B without the NA.

Sub NoNA()
'Determine last used row in Column A
 lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows in Column A
   For rw = 1 To lastRw
'If value is not NA, increment Row counter for
'Column B and copy the value
    If Cells(rw, 1) <> "NA" Then
      nxtRw = nxtRw + 1
      Cells(nxtRw, 2) = Cells(rw, 1)
    End If
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
July 23, 2012 at 04:51:37
Thanks a lot Derby.......You have solved my problem.......

Report •

Ask Question