Excel VBA：如何将单元格区域的格式及数据一同赋值给变量

现有一段Excel VBA代码，点击按钮时会复制Q145:AE211区域的数据，每次点击后将其连续粘贴到后续单元格区域。但该区域除数据外还包含单元格样式（颜色、边框）、自定义列宽及合并单元格格式，当前代码仅复制了数据。请问如何将这些格式内容与数据一同赋值给变量arr？

原代码如下：

Dim lastCol As Long, arr

 arr = Range("Q145:AE211").Value
 
 lastCol = Cells(1, Columns.Count).End(xlToLeft).Column + 16
 
 Cells(1, lastCol).Resize(UBound(arr), UBound(arr, 2)).Value = arr
 
 End Sub

解决方案 #

首先要明确：VBA中的Variant数组（也就是你代码里的arr）只能存储单元格的值，完全无法保存样式、边框、列宽、合并单元格这类格式信息，所以不存在把这些格式内容赋值给数组的方法。要实现完整复制数据+所有格式，得换用复制粘贴的方式：

改进后的代码 #

Sub CopyWithFullFormat()
    Dim sourceRng As Range
    Dim targetStartCol As Long
    Dim colCount As Integer
    
    ' 定义要复制的源区域
    Set sourceRng = Range("Q145:AE211")
    colCount = sourceRng.Columns.Count
    
    ' 计算目标区域的起始列：当前最后一列 + 源区域的列数
    targetStartCol = Cells(1, Columns.Count).End(xlToLeft).Column + colCount
    
    ' 复制源区域的全部内容（数据、样式、边框、合并单元格）
    sourceRng.Copy
    Cells(1, targetStartCol).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    
    ' 单独复制列宽，确保目标区域列宽和源区域一致
    sourceRng.Copy
    Cells(1, targetStartCol).PasteSpecial Paste:=xlPasteColumnWidths
    
    ' 清除剪贴板，避免后续操作受影响
    Application.CutCopyMode = False
End Sub

关键说明 #

• xlPasteAllUsingSourceTheme：这个粘贴类型会完整复制源区域的所有内容，包括数据、单元格颜色、边框、合并单元格格式等
• xlPasteColumnWidths：单独处理列宽复制，因为xlPasteAllUsingSourceTheme不会自动复制列宽
• 用sourceRng.Columns.Count代替硬编码的16，这样如果源区域的列数发生变化，代码不需要手动修改，适应性更强

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