Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looping Autofilter & deleting (Excel 2002)

    Hi,

    I have an autofilter which filters a sheet depending on what the user selects from a list on another worksheet(could be upto 100+ selections) and this loops through until it comes to a cell which contains "". This works fine. What I need to do next is when the code autofilters, I need to delete the selection and then move on in the loop to the next selection and do the same thing etc.

    What is the best way to do this, it has to be dynamic as the size of the selection will vary?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Looping Autofilter & deleting (Excel 2002)

    > depending on what the user selects from a list on another worksheet

    Can you provide details? What kind of list is that? A list box from the Forms toolbar, or from the Control Toolbox, or something else?

  3. #3
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping Autofilter & deleting (Excel 2002)

    Hans,

    Please see attached input sheet. Basically, the user selects their service and subsets of that service via data validation Yes/No boxes. This is then "translated" via some worksheet calcs to the right of their selections, column AG (which would be hidden normally).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Looping Autofilter & deleting (Excel 2002)

    By "deleting" the selection, do you mean that you want to reset the cells to No?

    It would be helpful if you could post a workbook that shows what you are doing, including the code.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Looping Autofilter & deleting (Excel 2002)

    I am not sure what you want the code to do and when you want to do it.

    Could you walk us thru what you want to happen and when? What will the user do, what should excel/vb do?

    I see one possible "stumbling block" (how bad it is depends on exactly what you want to do) in that changing a datavalidation triggers no trappable event, so you can not have any automatic code dependent on someone changing validation...

    Steve

  6. #6
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping Autofilter & deleting (Excel 2002)

    I appologise for my bad description of the issue. Please see attached. The user selects what he wants to delete from the Selection sheet at the moment they would press the Smiley face to run the macro, but this would be part of another procedure later on. The items selected are then deleted from the Data sheet (or they should be, at the moment it just auto filters on what the user selected).

    Does this help?

  7. #7
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping Autofilter & deleting (Excel 2002)

    Also I would like to delete the whole row of the relevant selection i.e. of what has been autofiltered. Probably should have mentioned that earlier, sorry.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Looping Autofilter & deleting (Excel 2002)

    Code like this:
    <pre>With Worksheets("data")
    Intersect(.Rows("2:65536"), _
    .Cells.SpecialCells(xlCellTypeVisible).EntireRow). Delete
    End With</pre>


    will delete the visible rows (from 2- end) of the rows in data. The intersect is used to prevent deletion of row 1.

    Steve

  9. #9
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping Autofilter & deleting (Excel 2002)

    Thanks, that seems to do the trick.

Posting Permissions

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