Yes or No?All that form a sequence
A formula will return TRUE/FALSE:In any situation from 1 to 99
=ISNUMBER(FIND(B3,TEXTJOIN(,TRUE,SEQUENCE(99))))
maybe I'm just a DNA sequence?And I don't at all understand how "p45cal" is a sequence.
Yuk.excel 2010
The code below operates on whatever is selected on that sheetNow you have to define what you mean by 'delete'
Private Sub CommandButton1_Click()
Dim rngToDelete As Range
y = Join([transpose(row(1:99))], "")
For Each cll In Selection.Cells
If InStr(y, CStr(cll.Value)) > 0 Then
'cll.Offset(, 1).Value = "delete me"
If rngToDelete Is Nothing Then Set rngToDelete = cll Else Set rngToDelete = Union(rngToDelete, cll)
End If
Next cll
If rngToDelete Is Nothing Then
MsgBox "Nothing to delete"
Else
rngToDelete.Select 'you will want to do something other than select the cells depending on what you mean by 'delete'.
End If
End Sub
' Look at the number in col B as a series of single digits, then as 2-digit numbers, then 3, and so forth up to an arbitrary limit.
Const Upper = 5 'we'll stop looking after we've tried for 5-digit numbers
If IsNumber(nbr) then str = nbr & "" Else str = nbr 'convert to string
lstr = Len(str)
For jl = 1 to Upper
If lstr Mod jl <> 0 then Goto IterateLen 'the value has be a multiple of the length we're testing
LastVal = Int(Left(str, jl)) 'get the first JL bytes of the string
For jp = jl + 1 To lstr Step jl 'start comparing to the next JL bytes
sub = Int(Mid(str, jp, jl))
If sub <> LastVal + 1 then Goto IterateLen 'this JL digits not in sequence with the previous JL digits
LastVal = sub 'save the substring for the next iteration
Next jp
' If we get here, it's in sequence.
IterateLen:
Next jl
Private Sub CommandButton1_Click()
Dim rngToDelete As Range
For Each rw In Selection.Rows
IsSequential = True
myVals = rw.Value
c1 = myVals(1, 1)
For c = 2 To UBound(myVals, 2)
If myVals(1, c) <> c1 + c - 1 Then
IsSequential = False
Exit For
End If
Next c
If IsSequential Then If rngToDelete Is Nothing Then Set rngToDelete = rw Else Set rngToDelete = Union(rngToDelete, rw)
Next rw
If rngToDelete Is Nothing Then
MsgBox "Nothing to delete"
Else
rngToDelete.Select 'you will want to do something other than select the cells depending on what you mean by 'delete'.
End If
End Sub
What was selected before you ran the macro?The initial selection needs to be at least 2 columns wide and can be as wide as you want.
Select the cells on the sheet that you want to process BEFORE you run the macro. That's range E4:J22 in your sample file.Did not select. Showed in yellow what I sent you