Office 365共享Excel工作簿新增数据覆盖行问题排查与解决

问题背景 #

我有一个Office 365共享Excel文档，通过「Add Job」工作表的宏向「Job History」工作表添加新任务（宏会根据输入条件计算部门日期）。点击添加任务按钮时，本应定位到「Job History」的下一行空行，将相关数据转置至新行。该功能大部分场景下正常，但当多用户同时打开文档，且部分用户对「Job History」工作表应用筛选时，会出现覆盖已有数据行而非插入空行的异常情况。

原核心代码片段：

Dim lastrow As Integer
lastrow = WSJobHist.Cells(Rows.Count, "A").End(xlUp).Row

Dim i As Integer
i = 1
For Each c In rng
    WSJobHist.Cells(lastrow + 1, i) = c
    i = i + 1
Next

问题原因 #

• 筛选状态下End(xlUp)的局限性：当「Job History」应用筛选后，Cells(Rows.Count, "A").End(xlUp).Row只会返回可见区域的最后一行行号，但筛选隐藏的行中可能仍有数据，此时lastrow + 1可能指向隐藏的已有数据行，导致覆盖。
• 共享文档的同步延迟：多用户同时操作时，本地计算的lastrow可能不是服务器端的真实最后一行，尤其是有用户筛选时，本地视图的行号和实际数据行号存在偏差，进一步加剧覆盖问题。
• 未明确指定工作表范围：原代码中Rows.Count没有绑定到WSJobHist，可能因当前激活工作表不同导致行号计算错误。

解决方案 #

方案1：将「Job History」转换为Excel表格（推荐） #

Excel表格（ListObject）会自动管理数据行，无论是否筛选，添加新行都会定位到表格的真实末尾，且在共享文档中同步更可靠。

修改后的核心代码：

mm = Application.ScreenUpdating
Application.ScreenUpdating = False

' 假设「Job History」的表格名称为Table_JobHistory（可自行修改）
Dim jobTable As ListObject
Set jobTable = WSJobHist.ListObjects("Table_JobHistory")

' 添加新行并获取该行对象
Dim newRow As ListRow
Set newRow = jobTable.ListRows.Add(AlwaysInsert:=True)

' 将数据写入新行
Dim i As Integer
i = 1
For Each c In rng
    newRow.Range.Cells(1, i).Value = c.Value
    i = i + 1
Next

Application.ScreenUpdating = mm

方案2：修正lastrow的计算逻辑（不使用表格的情况） #

如果不使用表格，需计算真实的最后一行（包含隐藏行），同时锁定操作区域避免多用户冲突：

mm = Application.ScreenUpdating
Application.ScreenUpdating = False

' 计算包含隐藏行的真实最后一行
Dim lastrow As Long
lastrow = WSJobHist.Cells(WSJobHist.Rows.Count, "A").End(xlUp).Row

' 临时锁定「Job History」的最后一行区域，避免多用户同时写入
WSJobHist.Range("A" & lastrow + 1).Resize(1, rng.Count).Locked = True
WSJobHist.Protect UserInterfaceOnly:=True

' 写入数据
Dim i As Integer
i = 1
For Each c In rng
    WSJobHist.Cells(lastrow + 1, i).Value = c.Value
    i = i + 1
Next

' 解锁区域
WSJobHist.Unprotect
Application.ScreenUpdating = mm

额外优化建议 #

• 将Integer类型改为Long：Excel的行号可能超过Integer的最大值（32767），避免溢出错误。
• 明确绑定所有范围到指定工作表：比如原代码中的Range("D3:D10,D13,D21:D25,E12,E21:E23,E23:E25")需改为WSAddJob.Range("D3:D10,D13,D21:D25,E12,E21:E23,E23:E25")，防止因当前激活表不同导致范围错误。
• 添加错误处理：在宏中加入On Error Resume Next和On Error GoTo 0，处理共享文档中的同步冲突。

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