Copy cell only is value is not N/A

[kingconsto]

Hey guys,

I am trying to write a macro that will copy all the cells in a column only if the values are not N/A. For example in column A I have,

Data
Data
n/a
Data
Data
Data
n/a
Data

I would like to copy the 6 cells (example only, could be 6 or 8 or more or less) that have data and paste it in column Z. I would like to code to loop and do the same for column B. The data in column B should continue down the list in column z after all the data from column A. So essentially

Column A Data
Column A Data
Column A Data
Column A Data
Column A Data
Column B Data
Column B Data
etc.

Thanks for the help!

 

[vmjan02]

sub select()
' considering the data is in column a
range("a1").select
Selection.AutoFilter Field:=1, Criteria1:="#N/A"
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
'copying the data in column z
range("a1").select
selection.autofilter
range("z1").select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
end sub



Try this for column A then we can do it further.

 

[vmjan02]

sorry use this one

sub select()
Range("a1").Select
Selection.AutoFilter Field:=1, Criteria1:="#N/A"
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Range("Z1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Selection.AutoFilter
end sub

 

[kingconsto]

Thanks for the response. Good news is that the data copied, bad news is that it copied and pasted the N/As as well. I do not know if this makes a difference but the data in column A are all hlookup formulas. Those that emit the N/A I want to exclude.

 

[vmjan02]

Thanks for the response. Good news is that the data copied, bad news is that it copied and pasted the N/As as well. I do not know if this makes a difference but the data in column A are all hlookup formulas. Those that emit the N/A I want to exclude.

Just change this code

Selection.AutoFilter Field:=1, Criteria1:<>"#N/A"

 

[kingconsto]

I am getting a compile error with this code

 

[kingconsto]

I changed it to Selection.AutoFilter Field:=1, Criteria1:="<>n/a"
Thanks!

 

[kingconsto]

How do I get it to do the same this for column B and have it copy below where ever column Z ended?

 

[vmjan02]

now use the same code for column B as well

Range("B1").Select
Selection.AutoFilter Field:=1, Criteria1:="<>#N/A"
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Range("Z1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B1").Select
Selection.AutoFilter