使用VBA清洗Excel数据时遇Offset行错误，求解决方法

报错原因 #

1. 硬编码循环行数超出实际数据范围：代码中For jCounter = 0 To 72865是固定值，若实际数据行数少于72866行，Offset会指向不存在的单元格，触发错误。
2. 列数获取逻辑错误：
   • 变量名iEndRow语义混淆，实际存储的是列数，易引发逻辑误解。
   • 使用End(xlToRight)获取最后一列时，若目标行存在空列，会提前终止查找，导致iCounter循环到超出实际列数的位置，iCounter +1自然指向无效单元格。
3. 目标行计数逻辑错误：遇到"Record"行时iRowClean = iRowClean +2，会导致Clean_Data工作表产生大量空行，且后续变量值无法对应到正确的记录行。

修正后的代码 #

Sub cleandata()
    Dim sVariable As String
    Dim vEntry As Variant
    Dim jCounter As Long
    Dim iCounter As Long
    Dim iEndCol As Long '修正变量名，明确存储列数
    Dim sRecord As String
    Dim iRowClean As Long
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    
    '绑定工作表对象，避免重复调用Sheets方法，同时检查工作表是否存在
    On Error Resume Next
    Set wsSource = ThisWorkbook.Sheets("customer_service_Anheuser-Busch")
    Set wsTarget = ThisWorkbook.Sheets("Clean_Data")
    On Error GoTo 0
    
    If wsSource Is Nothing Or wsTarget Is Nothing Then
        MsgBox "源工作表或目标工作表不存在，请检查！"
        Exit Sub
    End If
    
    iRowClean = 0
    '动态获取源数据最后一行，避免硬编码
    Dim lastRow As Long
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    For jCounter = 0 To lastRow - 1 '从第1行（A1）偏移0开始，到最后一行偏移量为lastRow-1
        sRecord = wsSource.Range("A1").Offset(jCounter, 0).Value
        
        If Left(sRecord, 6) <> "Record" Then
            '获取当前行最后一列，从行尾向左找，避免空列影响
            iEndCol = wsSource.Cells(jCounter + 1, wsSource.Columns.Count).End(xlToLeft).Column
            '循环变量列，步长2，确保不超出列数
            For iCounter = 1 To iEndCol - 1 Step 2
                '先检查单元格是否存在
                If iCounter + 1 <= iEndCol Then
                    sVariable = wsSource.Range("A1").Offset(jCounter, iCounter).Value
                    vEntry = wsSource.Range("A1").Offset(jCounter, iCounter + 1).Value
                    
                    Select Case sVariable
                        Case "Rainfall (Inches)"
                            wsTarget.Range("B1").Offset(iRowClean, 0) = vEntry
                        Case "Avg. Education (Yrs.)"
                            wsTarget.Range("C1").Offset(iRowClean, 0) = vEntry
                        Case "Month"
                            wsTarget.Range("D1").Offset(iRowClean, 0) = vEntry
                        Case "Avg. Household Size"
                            wsTarget.Range("E1").Offset(iRowClean, 0) = vEntry
                        Case "Population (000s)"
                            wsTarget.Range("F1").Offset(iRowClean, 0) = vEntry
                        Case "County"
                            wsTarget.Range("G1").Offset(iRowClean, 0) = vEntry
                        Case "Unemployment Rate"
                            wsTarget.Range("H1").Offset(iRowClean, 0) = vEntry
                        Case "Num. Microbreweries"
                            wsTarget.Range("I1").Offset(iRowClean, 0) = vEntry
                        Case "Avg. Income (000s)"
                            wsTarget.Range("J1").Offset(iRowClean, 0) = vEntry
                        Case "% Home Owners"
                            wsTarget.Range("K1").Offset(iRowClean, 0) = vEntry
                        Case "% White"
                            wsTarget.Range("L1").Offset(iRowClean, 0) = vEntry
                        Case "Avg. Commute (mins)"
                            wsTarget.Range("M1").Offset(iRowClean, 0) = vEntry
                        Case "% Female"
                            wsTarget.Range("N1").Offset(iRowClean, 0) = vEntry
                        Case "% Democrats"
                            wsTarget.Range("O1").Offset(iRowClean, 0) = vEntry
                        Case "Log of Help Desk Employees*"
                            wsTarget.Range("P1").Offset(iRowClean, 0) = vEntry
                        Case "Avg. Employee Wage (000s)"
                            wsTarget.Range("Q1").Offset(iRowClean, 0) = vEntry
                        Case "Year"
                            wsTarget.Range("R1").Offset(iRowClean, 0) = vEntry
                        Case "Log of Complaints (per day)*"
                            wsTarget.Range("S1").Offset(iRowClean, 0) = vEntry
                        Case "Avg. Worker Hours (per day)*"
                            wsTarget.Range("T1").Offset(iRowClean, 0) = vEntry
                        Case "Num. Starbucks"
                            wsTarget.Range("U1").Offset(iRowClean, 0) = vEntry
                        Case "Avg. Age"
                            wsTarget.Range("V1").Offset(iRowClean, 0) = vEntry
                    End Select
                End If
            Next iCounter
        Else
            '遇到Record行，下一条记录对应新行，只需加1
            iRowClean = iRowClean + 1
        End If
    Next jCounter
End Sub

额外优化说明 #

• 绑定工作表对象，减少重复调用Sheets方法的开销，同时增加工作表存在性检查，避免因工作表缺失报错。
• 用Select Case替代冗长的ElseIf链，代码更易读和维护。
• 增加列数边界检查，确保iCounter +1不超出当前行的实际列数。
• 动态获取源数据最后一行，彻底避免硬编码行数导致的越界问题。

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