+ Reply to Thread
Results 1 to 4 of 4

Purpose of the "If Not Intersect" statement

  1. #1
    Registered User
    Join Date
    05-02-2006
    Posts
    95
    Quote Originally Posted by Reafidy
    How about putting advanced filter code into a worksheet change event so every time the raw data in column a is updated advanced filter will filter unique values to column b.

    Please Login or Register  to view this content.
    This is a good code. Could somebody please explain the purpose If Not Intersect...? The event seems to work with or without it.
    More importantly, when I clear the contents within Column 1, I get a debug error prompt. I do need to manually clear the entire contents (and I need to "B1" range to auto clear at the same time).

    Thanks,
    Ricky

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    ExcelQuestion,

    Please read forum rules below. I've moved yout post as you should always start a new thread with a link to the thread you have a question to

    See link for more on the intersect method
    http://www.ozgrid.com/VBA/vba-intersect.htm

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Quote Originally Posted by ExcelQuestion
    This is a good code. Could somebody please explain the purpose If Not Intersect...? The event seems to work with or without it.
    More importantly, when I clear the contents within Column 1, I get a debug error prompt. I do need to manually clear the entire contents (and I need to "B1" range to auto clear at the same time).

    Thanks,
    Ricky
    Hi Riccy,

    The 'Not Intersect' ensures that the code only runs if a change is made in column A. Changes other than in col A have no effect. If you leave the Not intersect bit out then the code will run whenever a cell anywhere is changed.

    The problem you have with clearing column A is associated with the Advanced Filter which needs a column header to work. Obviously if you clear the entire contents of column A, there is no column header for the Advanced filter to work with - hence the error. You could use the following slightly amended code which will check whether you are deleting more than one cell from column A, and if so it will delete ALL of column A and B.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    05-02-2006
    Posts
    95
    Quote Originally Posted by Richard Buttrey
    Hi Riccy,

    The 'Not Intersect' ensures that the code only runs if a change is made in column A. Changes other than in col A have no effect. If you leave the Not intersect bit out then the code will run whenever a cell anywhere is changed.

    The problem you have with clearing column A is associated with the Advanced Filter which needs a column header to work. Obviously if you clear the entire contents of column A, there is no column header for the Advanced filter to work with - hence the error. You could use the following slightly amended code which will check whether you are deleting more than one cell from column A, and if so it will delete ALL of column A and B.

    Please Login or Register  to view this content.
    Thanks VBANoob.

    Thanks Richard,
    Your code works nicely and helps me a great deal.

    Much appreciate it,
    Ricky

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1