如何用Excel VBA避免提取唯一值时出现#NV错误值？

原代码直接赋值时出现#N/A，通常有两个原因：一是源数据包含错误值（如#N/A、#VALUE!等），Unique函数会保留这些错误；二是目标区域范围大于Unique返回的结果行数，多余单元格自动填充#N/A。以下是两种针对性的写法：

写法1：过滤源数据错误值后提取唯一值 #

先筛选出源区域中无错误的行，再提取唯一值，避免错误值被带入目标区域：

Dim sourceRng As Range, filteredData As Variant, uniqueData As Variant
Dim lastRow As Long

' 定位源数据最后一行并定义区域
lastRow = ThisWorkbook.Sheets("test_source").Cells(Rows.Count, "J").End(xlUp).Row
Set sourceRng = ThisWorkbook.Sheets("test_source").Range("J2:K" & lastRow)

' 过滤掉含错误值的行
filteredData = Application.WorksheetFunction.Filter(sourceRng.Value, _
             Not IsError(Application.WorksheetFunction.Index(sourceRng.Value, 0, 1)) _
             And Not IsError(Application.WorksheetFunction.Index(sourceRng.Value, 0, 2)))

' 提取唯一值并写入目标区域（过滤后有数据才执行）
If Not IsEmpty(filteredData) Then
    uniqueData = Application.WorksheetFunction.Unique(filteredData)
    ThisWorkbook.Sheets("test_destination").Range("J2").Resize(UBound(uniqueData, 1), 2).Value = uniqueData
End If

写法2：动态匹配目标区域行数 #

如果源数据本身无错误值，只是目标区域范围过大导致多余单元格显示#N/A，只需根据Unique返回的结果大小动态设置目标区域：

Dim sourceData As Variant, uniqueData As Variant
Dim lastRow As Long

lastRow = ThisWorkbook.Sheets("test_source").Cells(Rows.Count, "J").End(xlUp).Row
sourceData = ThisWorkbook.Sheets("test_source").Range("J2:K" & lastRow).Value

' 使用Application.Unique避免空数据时抛出错误
uniqueData = Application.Unique(sourceData)

' 清空目标区域旧数据
ThisWorkbook.Sheets("test_destination").Range("J2:K" & ThisWorkbook.Sheets("test_destination").Cells(Rows.Count, "J").End(xlUp).Row).ClearContents

' 写入唯一值，适配结果行数
If IsArray(uniqueData) Then
    ThisWorkbook.Sheets("test_destination").Range("J2").Resize(UBound(uniqueData, 1), 2).Value = uniqueData
Else
    ThisWorkbook.Sheets("test_destination").Range("J2:K2").Value = uniqueData
End If

补充说明 #

• Application.Unique比Application.WorksheetFunction.Unique更灵活：源数据为空或无唯一值时，不会触发运行时错误，而是返回单个值或空数组。
• 写入前清空目标区域旧数据，避免新旧数据混杂。

内容的提问来源于stack exchange，提问作者BlankerHans