• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete rows with numbers in sequence

BobBridges

Active Member
If those red '<' symbols at the right are supposed to mean anything, SPP, I don't understand what. "In sequence" can mean at least two things:
1) "Delete a row if col B is greater than the value on the previous row". Using that definition, I'd delete rows 3, 4, 7, 10, 12, 13, 15-18 and 20.
2) "Delete a row if col B is greater than the value of the last row deleted". Then I'd delete 3, 4, 7, 17 and 18.

Or maybe you have some other definition in mind.
 

BobBridges

Active Member
No, I already pointed out how "sequence" could mean at least two different things, so it's no help just repeating the word. You'll have to describe what you want in some way that isn't ambiguous. If you don't understand what I wrote above, try reading it and thinking about it carefully. Feel free to ask questions, if it would help.
 

BobBridges

Active Member
I just noticed your native language is Portuguese. I don't think I can write clearly in Portuguese, but if it's difficult to describe what you mean in English, I may be able to read in your language.
 

p45cal

Well-Known Member
Would you delete 89101112 ?
And would you delete 891892893 ?
What about 1000110002 ?
 
Last edited:

BobBridges

Active Member
Ah, I didn't even think of that. I assumed he meant his program should delete rows that are in sequence with other, previous rows. It never occurred to me he might have mean to delete a row if the digits of the number in col B were all in sequence. Yet a third possible meaning.
 

p45cal

Well-Known Member
This too is a sequence:
58132134
(part of the fibonacci sequence) so that must be deleted too?
As BobBridges says, you HAVE to define what you mean by 'sequence'.
 

BobBridges

Active Member
Hm, this is more complicated. It's easy enough to write a program to check whether each digit of a number is one greater than the previous digit. But you want it also to check whether each two digits are one greater than the previous two digits, and presumably with three digits and four and so on.

And I don't at all understand how "p45cal" is a sequence.

This'll be a test: If you can explain that last one, I might be able to help. If all you can do is repeat "sequence", I'll give up, on the assumption that you are unable to describe in words what you want. (Your language skills haven't given me much hope of it so far. As I said, feel free to try to describe it in Portuguese.)
 

p45cal

Well-Known Member
In any situation from 1 to 99
A formula will return TRUE/FALSE:
Code:
=ISNUMBER(FIND(B3,TEXTJOIN(,TRUE,SEQUENCE(99))))
which can be used in a macro.
Now you have to define what you mean by 'delete'; the entire row on the sheet, just the cells in columns A and B and have the ones below shift up, or just clear the contents (and/or formula) in those cells. Maybe you want just the values deleted in column B leaving blank cells?
 

p45cal

Well-Known Member
excel 2010
Yuk.

Again,
Now you have to define what you mean by 'delete'
The code below operates on whatever is selected on that sheet
Code:
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
Observe the comments.
 

BobBridges

Active Member
Oh, I didn't notice your handle so I didn't recognize that he was addressing his post to you. Thought he was saying "p45cal" was supposed to be another example of a sequence.

Well, it seems to me this could be done easily enough. Something like this:
Code:
  ' 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
 

p45cal

Well-Known Member
Code:
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
The above code will work on the selection before you run it (E4:J22 in your sample file). It will look at each row of that selection and decide if the cells within it are in ascending sequence left to right. If they are, that row will be selected at the end of the macro. The initial selection needs to be at least 2 columns wide and can be as wide as you want.
 

p45cal

Well-Known Member
Did not select. Showed in yellow what I sent you
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.
If only a single cell or a single column is selected when you run the macro you will get that error.
 
Top