执行下一步前的Excel数据完整性验证需求

我有一个包含三列的Excel工作表：A列（账号）、B列（C代表贷记，D代表借记）、C列（金额）。当前流程可正常运行，但当某行仅单个列有数据时，下一步仍会执行。我希望当工作表中存在不完整行时，向用户抛出错误提示。

原VBA代码 #

Option Explicit

Public no_of_rows As Long               'No. of records (rows) in the file

Dim rowcount As Long                    'No. of records (rows) in the file (stores no_of_rows variable value)
Dim counter As Long                     'Counter variable
Dim r As Long, rng As Range
Dim error As Boolean

Private Sub CommandButton1_Click()
    
    Application.ScreenUpdating = False
    
    Range("A65536").End(xlUp).Offset(1, 0).Select
    no_of_rows = CLng(ActiveCell.Row) - 1
    
    Range("B65536").End(xlUp).Offset(1, 0).Select
    If (CLng(ActiveCell.Row) - 1) > no_of_rows Then
        no_of_rows = CLng(ActiveCell.Row) - 1
    End If
    
    ActiveCell.Offset(-(CLng(ActiveCell.Row) - 1), -1).Select
    If no_of_rows = 1 And Trim(ActiveCell.Value) = "" And Trim(Range("B" & CLng(ActiveCell.Row)).Value) = "" Then
        no_of_rows = 0
    Else
        counter = 0
        While (counter < no_of_rows)
            If Trim(ActiveCell.Value) = "" Or Trim(Range("B" & CLng(ActiveCell.Row)).Value) = "" Then
                Selection.EntireRow.Delete
                no_of_rows = no_of_rows - 1
            Else
                counter = counter + 1
                ActiveCell.Offset(1, 0).Select
            End If
        Wend
    End If
    
    ActiveCell.Offset(-(CLng(ActiveCell.Row) - 1), 0).Select
    If no_of_rows = 0 Then
        MsgBox "File contains no records to process.", vbCritical, "Error!"
        GoTo Trap
    End If
    
    error = False
    If (error = True) Then
        GoTo Trap
    End If
    
    ttumform.Show
    Application.ScreenUpdating = True
    
    Exit Sub
    
Trap:
    Application.ScreenUpdating = True
End Sub

修改后的代码（实现不完整行报错） #

Option Explicit

Public no_of_rows As Long               '记录总行数
Dim counter As Long                     '计数器变量
Dim hasError As Boolean                 '标记是否存在错误

Private Sub CommandButton1_Click()
    
    Application.ScreenUpdating = False
    hasError = False
    
    '获取数据总行数（取A、B、C列中最大的行数）
    no_of_rows = WorksheetFunction.Max(Range("A65536").End(xlUp).Row, _
                                       Range("B65536").End(xlUp).Row, _
                                       Range("C65536").End(xlUp).Row)
    
    '如果没有数据行
    If no_of_rows = 0 Then
        MsgBox "文件中没有可处理的记录。", vbCritical, "错误！"
        GoTo Trap
    End If
    
    '遍历所有数据行，检查是否有不完整行
    For counter = 1 To no_of_rows
        '检查当前行A、B、C列是否都非空（去除首尾空格后）
        If Trim(Cells(counter, "A").Value) = "" Or _
           Trim(Cells(counter, "B").Value) = "" Or _
           Trim(Cells(counter, "C").Value) = "" Then
            hasError = True
            Exit For '找到错误行就停止遍历
        End If
    Next counter
    
    '如果存在不完整行，抛出错误提示
    If hasError Then
        MsgBox "第 " & counter & " 行数据不完整，请检查账号、借贷标识和金额列。", vbCritical, "数据错误！"
        GoTo Trap
    End If
    
    '没有错误则继续执行原有流程
    ttumform.Show
    Application.ScreenUpdating = True
    
    Exit Sub
    
Trap:
    Application.ScreenUpdating = True
End Sub

修改说明 #

• 新增对C列（金额）的检查，确保每行账号、借贷标识、金额三列都有有效数据
• 将error变量名改为hasError，避免与VBA内置关键字冲突
• 改用For循环遍历行，替代原代码的While+Select操作，提升效率并避免单元格选中的潜在问题
• 检测到不完整行时直接定位行号，方便用户快速排查
• 移除原代码中删除空行的逻辑，改为检测到问题就立即报错终止流程

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