+ Reply to Thread
Results 1 to 4 of 4

Counting a number series plus row delete

  1. 12-07-2006, 08:37 PM #1
    Vlad999
    Vlad999 is offline
    Forum Contributor
    Join Date
    04-18-2006
    Posts
    135

    Counting a number series plus row delete

    Hi

    I have a list of random numbers in column A the number range is from 0 to 20,000. What I am trying to do is to count the sequential numbers after each random number....sorry this is not really explained well so I willl use a numeric example.

    A
    23
    24
    25
    33
    34
    60
    77
    80

    Above is what I have in column A you can see that my number range starts a 23 so I would like to count the sequential numbers that occur from 23 in this case it would be count(23,24,25) giving the answer 3 I would like this answer to be inserted into column B next to 23 and the numbers that were counted in this formula then be deleted as rows (i.e. where the number 24 and 25 was counted then the rows containing these numbers are deleted), The macro moves then to count the next number.

    In the case of just a single number then a value of 1 is entered into the column next to the number indicating that there was only one number in that sequence.


    Can this be done? If anyone can work out a VBA for this that would be great but even a formula will do I will manually delete the rows if need be (but there 11000 of them and I'de rather not)
    Reply With QuoteRegister To Reply
  2. 12-07-2006, 09:27 PM #2
    Steel Monkey
    Steel Monkey is offline
    Forum Contributor
    Join Date
    11-23-2005
    Location
    Perth, Australia
    Posts
    218

    Counting a number series plus row delete

    Hi Vlad

    Thought i might have a crack at it but im a bit short on time. Here is what i came up with although this only covers one point, it gives you a sub total of the consecutive numbers but it does not delete them. Would you be able to delete column A once it has counted the consecutive numbers ?

    Havent had time to properly test this, but seems to work ok

    Anyway give it a whirl, maybe someone will get around ot putting up a better response, but hopefully this will suffice for the time being!

    Sorry i dont have time to finish it off!

    Let me knowo how it goes

    EDIT:This goes from cell A2, if you have a number in A1 change the range to A1:A20000

    Please Login or Register  to view this content.
    Last edited by Steel Monkey; 12-07-2006 at 09:45 PM.
    Reply With QuoteRegister To Reply
  3. 12-08-2006, 12:37 AM #3
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Steel Monkey
    Hi Vlad

    Thought i might have a crack at it but im a bit short on time. Here is what i came up with although this only covers one point, it gives you a sub total of the consecutive numbers but it does not delete them. Would you be able to delete column A once it has counted the consecutive numbers ?

    Havent had time to properly test this, but seems to work ok

    Anyway give it a whirl, maybe someone will get around ot putting up a better response, but hopefully this will suffice for the time being!

    Sorry i dont have time to finish it off!

    Let me knowo how it goes

    EDIT:This goes from cell A2, if you have a number in A1 change the range to A1:A20000

    Please Login or Register  to view this content.
    Hi,

    Sorry Steel Monkey, but couldn't understand why yours wasn't working, I might try again now that I've understood a little better. This code faults if the last row is a single, and counts 1 on the following space, but some other numbers seem to work.

    Please Login or Register  to view this content.
    off to play with Steel Monkey's version now,

    ---
    Si fractum non sit, noli id reficere.
    Reply With QuoteRegister To Reply
  4. 12-08-2006, 12:53 AM #4
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Steel Monkey
    Hi Vlad

    Thought i might have a crack at it but im a bit short on time. Here is what i came up with although this only covers one point, it gives you a sub total of the consecutive numbers but it does not delete them. Would you be able to delete column A once it has counted the consecutive numbers ?

    Havent had time to properly test this, but seems to work ok

    Anyway give it a whirl, maybe someone will get around ot putting up a better response, but hopefully this will suffice for the time being!

    Sorry i dont have time to finish it off!

    Let me knowo how it goes

    EDIT:This goes from cell A2, if you have a number in A1 change the range to A1:A20000

    Please Login or Register  to view this content.
    HI, - to complete the code of Steel Monkey, 
    Please Login or Register  to view this content.
    not elegant, but functional (the addition that was).
    remove the 'Stop' to complete the deletions.

    hth
    ---
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1