在excel vba中仅复制和粘贴非n#a单元格

Question

如何在没有#N/A数据的情况下将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

试一试，显然需要放入一个sub()

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

Dim rng As Range Dim crange As Range Dim lastrow As Double

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")