Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: conditional deletion

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If there are 4 cells of data per row:date, time, price and volume and we want to delete all rows except those rows which have a volume cell value of >= 1000, how is the best achieved?
    Thanks in advance.
    ptahl

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    not sure if it's *best*, as some VBA code will do it quicker, but autofiltering on that column should do the trick, leaving you the rows to delete manually


    :: Pharma Z - Family drugstore ::

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    To add to Chris's reply. If you autofilter using a custom criteria of <1000. Then highlight all the data and press F5 and then Special. Choose the Visible Cells Only option button. Then click Edit, Delete and choose Entire Row. That will delete all of the rows under 1000 in one go. Remove the autofilter and you'll be left with the data you want.

    Regards,
    Dan.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,593
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-31 06:44, ptahl wrote:
    If there are 4 cells of data per row:date, time, price and volume and we want to delete all rows except those rows which have a volume cell value of >= 1000, how is the best achieved?
    Thanks in advance.
    ptahl
    Insert 3 to 4 rows before your data.

    Supposing that the data area consists of columns A thru D,

    in A1 enter: Volume
    in A2 enter: >=1000

    Activate A4.
    Activate Data|Filter|Advanced Filter.
    Check Copy to another location.
    Assure that the List range covers your data area.
    Enter as Criteria range

    $A$1:$A$2

    Enter as value of Copy to, e.g.,

    $F$1

    Click OK.

    The area starting at $F$1 is what you're looking for. You can ditch everything else if you so desire.


    [ This Message was edited by: Aladin Akyurek on 2002-03-31 08:01 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi guys

    check

    http://www.mrexcel.com/board/viewtop...3863&forum=2&4

    Thiss my VBA i developed.. woth a look edit to delet not hide i guess wil do it...

    Hope some option....
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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