Excel VBA 复制数据到另一工作表时跳过空行的代码问题求助

核心修改逻辑 #

原有代码直接复制源区域全量内容，表单内的空行会同步粘贴到任务列表，修改后通过数组过滤掉源区域首列为空的行，再批量写入目标表，确保写入内容连续无空行。

完整修改后代码 #

Sub Task_Entry()

Application.ScreenUpdating = False

Dim InstalDesc As String
Dim AssignedTo As String
Dim Model As Range
Dim Drawing As Range
Dim Index As Long
Dim m As Long, n As Long
' 新增变量用于数组处理
Dim modelArr As Variant, drawingArr As Variant
Dim i As Long, targetRow As Long

Application.ScreenUpdating = False
' 从输入表单复制数据到任务列表
Sheets("Task Entry Form").Select
InstalDesc = Range("D3")
AssignedTo = Range("G2")
Set Model = Range("D5", Cells(Rows.Count, "D").End(xlUp)).Resize(, 2)
Set Drawing = Range("I5", Cells(Rows.Count, "I").End(xlUp)).Resize(, 2)

Index = Range("Q2")
With Sheets("Task List")
    ' 获取目标表最后一行行号
    n = .Range("D:X").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    If n = 3 Then n = 4 Else n = n + 2
    ' 标记标题行背景色
    .Range("A" & n & ":Z" & n).Interior.Color = 15189684
    .Cells(n, "D") = InstalDesc & " Summary"
    
    ' 替换原有剪贴板复制逻辑，先过滤Model区域空行再写入
    targetRow = n + 1
    modelArr = Model.Value
    For i = 1 To UBound(modelArr, 1)
        If Trim(modelArr(i, 1)) <> "" Then
            .Cells(targetRow, "E") = modelArr(i, 1)
            .Cells(targetRow, "Q") = modelArr(i, 2)
            targetRow = targetRow + 1
        End If
    Next i
    
    ' 过滤Drawing区域空行，接着Model最后写入行继续写入
    drawingArr = Drawing.Value
    For i = 1 To UBound(drawingArr, 1)
        If Trim(drawingArr(i, 1)) <> "" Then
            .Cells(targetRow, "F") = drawingArr(i, 1)
            .Cells(targetRow, "Q") = drawingArr(i, 2)
            targetRow = targetRow + 1
        End If
    Next i

    ' 获取写入完成后目标表最后一行行号
    m = .Range("D:X").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Range("a2").Select
         
End With
Application.ScreenUpdating = True
Reset_Form

Sheets("Task Entry Form").Select

Range("D3").Select

End Sub

关键调整说明 #

• 去掉了剪贴板Copy/Paste操作，改为数组读取写入，执行效率更高，也不会受剪贴板其他内容干扰
• 新增空行过滤逻辑：仅保留源区域首列非空的行写入目标表，自动跳过用户删除数据产生的空行
• 写入时自动顺延目标行号，保证Model和Drawing两块内容写入后全程无空行间隔

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