VBA拆分Excel为CSV时范围超已用区域如何避免复制空行

问题原因 #

原代码采用固定步长3000行的复制逻辑，每次循环都固定读取起始行往后3000行的范围，当最后一批剩余数据不足3000行时，超出实际数据边界的空行会被一同复制到新生成的CSV文件中，造成末尾多余空行。同时原代码还存在路径缺少分隔符、文件名编号与实际内容不匹配、关闭工作簿时重复触发保存提示的小问题。

修正后完整代码 #

Dim rLastCell As Range
Dim lLoop As Long, lCopy As Long
Dim wbNew As Workbook
Dim lEndRow As Long
Const BATCH_ROW_COUNT As Long = 3000 ' 单批次最大行数，可按需调整

With Sheets("Sheet1")
    Set rLastCell = .UsedRange.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
    ' 定义文件名中的日期字段，示例为当前日期，可按需修改取值规则
    fileDate = Format(Date, "yyyyMMdd")

    For lLoop = 2 To rLastCell.Row Step BATCH_ROW_COUNT
        lCopy = lCopy + 1
        ' 计算当前批次的实际结束行，不超过总数据的最后一行
        lEndRow = Application.Min(lLoop + BATCH_ROW_COUNT - 1, rLastCell.Row)
        
        Set wbNew = Workbooks.Add
        ' 复制表头行
        .Rows(1).EntireRow.Copy Destination:=wbNew.Sheets(1).Range("A1")
        ' 仅复制当前批次实际存在数据的行范围，不读取多余空行
        .Range(.Cells(lLoop, 1), .Cells(lEndRow, .Columns.Count)).EntireRow.Copy _
            Destination:=wbNew.Sheets(1).Range("A2")
        
        ' 保存为CSV文件，文件名编号匹配当前批次实际最后一行行号
        wbNew.SaveAs Filename:="C:\Users\Documents\" & _
            "product_catalog_" & fileDate & "_" & Format(lEndRow, "0000") & ".csv", _
            FileFormat:=xlCSV, _
            Local:=True
        ' 文件已保存，关闭时无需重复触发保存提示
        wbNew.Close SaveChanges:=False
    Next lLoop
End With

关键修改点 #

• 新增批次实际结束行计算逻辑：每轮循环取「起始行+2999」和「全表最后一行行号」的较小值作为复制范围的终点，剩余行数不足3000时不会越界读取空行
• 将单批次行数定义为常量，后续调整每批拆分的行数只需要修改常量值，不需要改动循环和复制逻辑
• 补全保存路径末尾的文件夹分隔符，避免文件被错误保存到上级目录
• 文件名中的行号改用当前批次实际结束行，避免最后一批文件名显示不存在的行号
• 调整工作簿关闭时的保存参数，避免重复弹出保存提示

内容的提问来源于stack exchange，提问作者Kristopher Pestaño