Excel VBA Userform代码修改求助：表头移至第29行后功能异常

核心修改逻辑 #

原代码适配表头在第1行、数据从第2行开始的场景，迁移到第29行后需要调整工作表引用、数据行偏移、列表框数据源绑定逻辑三个核心部分，修改后的完整代码如下：

Sub Refresh_Data()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("CID")
    Dim last_row As Long
    ' 改用End(xlUp)计算最后一行，避免A列前28行空值导致计数错误
    last_row = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row

    With Me.ListBox1
        .ColumnHeads = True
        .ColumnCount = 12
        .ColumnWidths = "30,100,100,70,100,100,50,100,50,50,120,200"
        
        ' 表头在第29行，数据从第30行开始
        If last_row = 29 Then
            .RowSource = "CID!A30:L30"
        Else
            .RowSource = "CID!A30:L" & last_row
        End If
    End With
End Sub

Private Sub Add_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("CID")
    Dim last_row As Long
    last_row = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
    
    ' 表单校验逻辑保持不变
    'Validations---------------------------------------------------------------------------------------
    If Me.TextBox1.Value = "" Then
        MsgBox "Please Fill Signal Name. If it is not required, fill -", vbCritical
        Exit Sub
    End If
    '------------------
    If Me.TextBox2.Value = "" Then
        MsgBox "Please Fill (From) Connector REF DES", vbCritical
        Exit Sub
    End If
    '------------------
    If Me.TextBox3.Value = "" Then
        MsgBox "Please Fill (From) Connector Pin Location", vbCritical
        Exit Sub
    End If
    '------------------
    If Me.TextBox4.Value = "" Then
        MsgBox "Please Fill Contact P/N or Supplied with Connector", vbCritical
        Exit Sub
    End If
    '------------------
    If Me.TextBox5.Value = "" Then
        MsgBox "Please Fill Wire Gauge", vbCritical
        Exit Sub
    End If
    '------------------
    If Me.TextBox6.Value = "" Then
        MsgBox "Please Fill Wire/Cable P/N", vbCritical
        Exit Sub
    End If
    '------------------
    If Me.TextBox7.Value = "" Then
        MsgBox "Please Fill (To) Connector REF DES", vbCritical
        Exit Sub
    End If
    '------------------
    If Me.TextBox8.Value = "" Then
        MsgBox "Please Fill (To) Pin Location", vbCritical
        Exit Sub
    End If
    '------------------
    If Me.TextBox9.Value = "" Then
        MsgBox "Please Fill Contact P/N or Supplied with Connector", vbCritical
        Exit Sub
    End If
    '------------------
    If Me.ComboBox10.Value = "" Then
        MsgBox "Use Drop Down Arrow to Select Wire Color", vbCritical
        Exit Sub
    End If
    '--------------------------------------------------------------------------------------------------
    
    ' 序号公式调整，减去表头所在行号29，保证第一条数据序号为1
    sh.Range("A" & last_row + 1).Value = "=Row()-29"
    sh.Range("B" & last_row + 1).Value = Me.TextBox1.Value
    sh.Range("C" & last_row + 1).Value = Me.TextBox2.Value
    sh.Range("D" & last_row + 1).Value = Me.TextBox3.Value
    sh.Range("E" & last_row + 1).Value = Me.TextBox4.Value
    sh.Range("F" & last_row + 1).Value = Me.TextBox5.Value
    sh.Range("G" & last_row + 1).Value = Me.TextBox6.Value
    sh.Range("H" & last_row + 1).Value = Me.TextBox7.Value
    sh.Range("I" & last_row + 1).Value = Me.TextBox8.Value
    sh.Range("J" & last_row + 1).Value = Me.TextBox9.Value
    sh.Range("K" & last_row + 1).Value = Me.ComboBox10.Value
    sh.Range("L" & last_row + 1).Value = Me.TextBox11.Value

    ' 清空表单控件
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.TextBox6.Value = ""
    Me.TextBox7.Value = ""
    Me.TextBox8.Value = ""
    Me.TextBox9.Value = ""
    Me.ComboBox10.Value = ""
    Me.TextBox11.Value = ""
    
    Call Refresh_Data
End Sub

关键修改点说明 #

• 工作表引用：所有原代码中引用Original工作表的部分全部替换为目标工作表CID
• 最后一行计算：放弃原CountA计数逻辑，改用End(xlUp)从A列最后一行向上查找实际数据末尾，避免表头上方空行导致计数错误
• 列表框数据源偏移：原数据从第2行开始，修改为从第30行（表头29行的下一行）开始
• 序号公式调整：原公式=Row()-1修改为=Row()-29，保证第一条录入数据的序号从1开始计算

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