VBA实现BOM表组件编码去重 动态汇总消耗量至Source表

需求说明 #

• 读取"BOM"工作表数据，对重复的组件编码去重，每个编码仅保留1条记录
• 汇总每个组件编码对应的组件用量总和
• 结果写入"Source"工作表，仅保留「组件编码」「组件消耗量」两列，无重复记录
• 代码需自动适配BOM表数据长度，无需手动调整数据范围

原有代码核心问题 #

• 硬编码固定3000行长度的存储数组，当唯一编码数超过3000时会触发越界报错
• 输出范围直接复用BOM表的总列数、总行数，会将BOM所有列都复制到Source表，不符合输出要求
• 将存储用量的数组字段定义为字符串类型，会导致数值汇总计算异常
• 未清空Source表历史数据，多次运行会残留旧内容
• 直接使用UsedRange取数容易受带格式的空单元格影响，导致范围识别不准

修改后可直接运行的代码 #

Private Sub consolidatedata()
    Dim bomSht As Worksheet, sourceSht As Worksheet
    Dim bomData As Variant
    Dim lastBomRow As Long, i As Long, outputRow As Long
    Dim codeMap As Object
    Dim currentCode As String, currentQty As Double
    
    ' 绑定工作表对象
    Set bomSht = ThisWorkbook.Sheets("BOM")
    Set sourceSht = ThisWorkbook.Sheets("Source")
    ' 用字典实现去重和汇总，无长度上限
    Set codeMap = CreateObject("Scripting.Dictionary")
    
    ' 动态获取BOM表组件编码列（A列）最后一行有效数据，自动适配任意数据长度
    lastBomRow = bomSht.Cells(bomSht.Rows.Count, "A").End(xlUp).Row
    ' 仅读取需要的编码、用量两列数据，默认第1行为表头，从第2行开始读
    bomData = bomSht.Range("A2:B" & lastBomRow).Value
    
    ' 遍历全量有效行完成汇总
    For i = LBound(bomData, 1) To UBound(bomData, 1)
        currentCode = Trim(CStr(bomData(i, 1)))
        ' 跳过编码为空的无效行
        If currentCode <> "" Then
            currentQty = Val(bomData(i, 2))
            If codeMap.Exists(currentCode) Then
                ' 编码已存在，累加用量
                codeMap(currentCode) = codeMap(currentCode) + currentQty
            Else
                ' 新编码，新增记录
                codeMap.Add currentCode, currentQty
            End If
        End If
    Next i
    
    ' 清空Source表原有旧数据，写入表头
    sourceSht.UsedRange.Clear
    sourceSht.Cells(1, 1) = "组件编码"
    sourceSht.Cells(1, 2) = "组件消耗量"
    
    ' 仅写入两列汇总结果，无重复编码
    outputRow = 2
    For Each codeKey In codeMap.Keys
        sourceSht.Cells(outputRow, 1) = codeKey
        sourceSht.Cells(outputRow, 2) = codeMap(codeKey)
        outputRow = outputRow + 1
    Next codeKey
    
    ' 释放对象
    Set codeMap = Nothing
    Set bomSht = Nothing
    Set sourceSht = Nothing
End Sub

关键修改说明 #

• 动态范围适配：通过A列向上定位最后一个非空单元格的方式获取有效数据行数，不受空格式单元格影响，自动适配任意长度的BOM数据
• 输出字段控制：仅读取、写入编码和用量两列，写入前自动清空Source表旧数据，不会带入多余列内容
• 去重逻辑优化：改用字典对象做去重汇总，没有固定行数限制，运行效率比原嵌套循环更高
• 数据类型修正：用量采用双精度数值类型存储计算，避免原字符串数组导致的计算错误
• 增加无效行过滤：自动跳过编码为空的行，避免无效数据进入结果表

如果你的BOM表表头不在第1行、编码/用量列不在A/B列，直接修改代码中对应的行号、列标参数即可适配。

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