vba - 如何检查VBA中的长度是否小于2

我的单元格中有两个破折号，我想在第二个破折号后检查多少个字符。如果长度或字符超过两个，则删除该行。

   Sub fi()

   Dim lastrow As Long, i As Long, firstD As Integer, secondD As Integer, bpno As Long

   lastrow = ActiveSheet.UsedRange.Rows.Count
       For i = 1 To lastrow
          firstD = InStr(1, ActiveSheet.Cells(i, 1), "-")
          secondD = InStr(firstD + 1, ActiveSheet.Cells(i, 1), "-")
          bpno = Mid(ActiveSheet.Cells(i, 1), firstD + 1, secondD - firstD)

            If Len(bpno) > 1 Then
              MsgBox ("yes")
            End If

       Next i


End Sub

最佳答案

您需要向后移动行，以使行号在循环中保持同步。

假设总是有2个破折号，请使用InStrRev获取最后一个的偏移量并与长度进行比较：

For i = lastrow To 1 Step -1
    value= ActiveSheet.Cells(i, 1).Value
    If Len(value) - InStrRev(value, "-") > 2 Then ActiveSheet.Rows(i).Delete
Next