只复制和粘贴Excel中的非n＃a个单元格vba

Question

如何将列数据A复制并粘贴到不含＃N/A数据的B列。

我有数据在A列

A   B 

8a61  8a61 
#N/A  fs 
fs  fttt 
fttt  dsff 
#N/A 
dsff 

复制粘贴非空白单元格，我用

Set wb = ActiveWorkbook 
    Set ws1 = wb.Sheets("Sheet7") 
    ws1.Range("A1:A1000").SpecialCells(xlCellTypeConstants).Copy ws1.Range("B1") 

如何复制粘贴柱数据，而不＃N/A

Answer 1

给这一去，显然需要放在一个子（）

Dim i As Integer, j As Integer 
j = 1 
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row 
If Not IsError(Cells(i, 1).Value) Then 
    Cells(j, 4) = Cells(i, 1) 
    j = j + 1 
End If 
Next i 

Answer 2

昏暗的RNG作为范围 昏暗CRANGE作为范围 昏暗LASTROW为双

Sheets("Sheet1").AutoFilterMode = False 

Set rng = Range("A1", Range("A2").End(xlDown)) 
lastrow = Range("A" & Rows.Count).End(xlUp).Row 
Set crange = Range("A2:A" & lastrow) 
rng.AutoFilter field:=1, Criteria1:="<>#N/A" 

crange.SpecialCells(xlCellTypeVisible).Copy Range("b2")