Excel UserForm ListBox空行删除判断失效问题及原理咨询

我在制作带有ListBox和删除按钮的Excel UserForm，ListBox从表格获取数据并以行形式显示。点击删除按钮时，已实现未选中行时提示用户选择行的功能，但添加选中空行时提示“此处无内容可删除”的功能一直未生效，脚本仍会继续执行。尝试多种判断语句均无效，最终通过替换vbTab的方法解决，原理如下：

原VBA代码 #

Private Sub Delete_Click()
    If Not ListBox1.Selected(ListBox1.ListIndex) Then
        MsgBox "Please select a row to delete"
        Exit Sub
    End If
    
    If Len(Trim(ListBox1.List(ListBox1.ListIndex))) = 0 Then '<-- 无效代码行
        MsgBox "Nothing to delete here"
        Exit Sub
    End If
    
    Dim response As VbMsgBoxResult
    response = MsgBox("Are you sure you want to delete the selected rows?", vbYesNo, "You sure?")
    
    'If no then do nothing
    If response = vbNo Then
        Exit Sub
    Else
        Dim selectedRows As New Collection
        Dim i As Integer
        Dim wb As Workbook
        Set wb = ThisWorkbook
    
        Dim ws As Worksheet
        Set ws = wb.Sheets("Table")

        'Store the indices of the selected rows in a collection
        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) Then
                selectedRows.Add i
            End If
        Next i
    
        'Delete the rows from the ListBox and the data source
        If selectedRows.Count > 0 Then
            'Sort the indices in descending order to avoid issues with deleting rows while iterating through the collection
            For i = selectedRows.Count To 1 Step -1
                ListBox1.RemoveItem selectedRows(i)
                Worksheets("Table").ListObjects("Table1").ListRows(selectedRows(i) + 1).Delete
            Next i
        End If
        Dim lastRow As Long
        lastRow = Worksheets("Table").Range("Q" & Rows.Count).End(xlUp).row 'find last filled cell in column Q

        Dim n As Long
        For n = 4 To lastRow ' start with Q4
            If Not IsEmpty(Worksheets("Table").Range("Q" & n)) Then
                Worksheets("Table").Range("Q" & n).Value = n - 3 'subtract 3 to start at 1 instead of 4
            End If
        Next n

        ListBox1.Clear
        Call UserForm_Initialize
    End If
End Sub

尝试过的无效判断语句 #

• 语句1：

  If ListBox1.ListIndex > -1 And Len(ListBox1.List(ListBox1.ListIndex)) = 0 Then
      MsgBox "Nothing to delete here"
      Exit Sub
  End If
  

• 语句2：

  If ListBox1.ListIndex > -1 And ListBox1.ListCount > 0 And Trim(ListBox1.List(ListBox1.ListIndex)) = "" Then
      MsgBox "Nothing to delete here"
      Exit Sub
  End If
  

• 语句3：

  If Not ListBox1.Selected(ListBox1.ListIndex) Then
      MsgBox "Please select a row to delete"
       Exit Sub
  ElseIf Len(ListBox1.List(ListBox1.ListIndex)) = 0 Then
      MsgBox "Nothing to delete here"
       Exit Sub
  End If
  

最终解决代码片段 #

ElseIf Len(Replace(ListBox1.List(ListBox1.ListIndex), vbTab, "")) = 0 Then
    MsgBox "Nothing to delete here"
     Exit Sub
End If

原理解释 #

你的ListBox应为多列模式（ColumnCount属性大于1），当用ListBox1.List(ListBox1.ListIndex)获取整行内容时，返回的是各列内容用vbTab（制表符）拼接的字符串。比如3列的空行，返回的是vbTab & vbTab & vbTab，而非空字符串。

之前的Trim()函数只能去除空格、换行等空白字符，无法处理制表符，所以Len(Trim(...))的结果不为0，导致判断失效。而用Replace(..., vbTab, "")把所有制表符替换为空后，再判断长度是否为0，就能准确识别所有列都是空的行，从而触发提示。

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