Copy cell only is value is not N/A

kingconsto

New Member
Joined
Apr 19, 2017
Messages
32
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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,023
Latest member
alabaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top