VBA将源区域可见单元格赋值到新区域时值重复如何解决

问题根因 #

Range.SpecialCells(xlCellTypeVisible) 在源区域存在隐藏列时，返回的是不连续的多区域集合，VBA直接读取这类集合的值时，只会返回第一个子区域的第一个单元格的内容，所以赋值到目标区域后所有单元格都会重复这个值。

可用解决方案 #

方案1：遍历可见单元格逐个赋值 #

适合数据量小的场景，逻辑简单不容易出错：

Dim visibleRng As Range, cell As Range, targetStartCol As Integer
' 定义源可见区域
Set visibleRng = Range("A1:C1").SpecialCells(xlCellTypeVisible)
' 目标区域起始列，E列对应序号5
targetStartCol = 5
For Each cell In visibleRng
    Cells(1, targetStartCol).Value = cell.Value
    targetStartCol = targetStartCol + 1
Next

方案2：数组中转批量赋值 #

适合数据量大的场景，运行效率更高：

Dim visibleRng As Range, cell As Range, resArr() As Variant, i As Integer
Set visibleRng = Range("A1:C1").SpecialCells(xlCellTypeVisible)
' 按可见单元格数量定义数组大小
ReDim resArr(1 To 1, 1 To visibleRng.Count)
i = 1
For Each cell In visibleRng
    resArr(1, i) = cell.Value
    i = i + 1
Next
' 批量赋值到目标区域
Range("E1").Resize(1, UBound(resArr, 2)).Value = resArr

方案3：Copy粘贴实现（不需要保留目标区域格式时可用） #

操作最简单，依赖剪贴板：

Range("A1:C1").SpecialCells(xlCellTypeVisible).Copy
Range("E1").PasteSpecial Paste:=xlPasteValues
' 清除剪贴板选中状态
Application.CutCopyMode = False

注意事项 #

赋值前建议先通过visibleRng.Count获取可见单元格的实际数量，动态调整目标区域的大小，避免出现值遗漏或者多余单元格空置的问题。

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