我的单元格中有两个破折号,我正在尝试在第二个破折号后检查有多少个字符。如果长度或字符超过两个,则删除该行。
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