+ Reply to Thread
Results 1 to 9 of 9

Auto Filter in VBA

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Auto Filter in VBA

    Hello everybody
    I have the following attachment
    When I select a number from Cell B3 I have the data related to that number.. everything is ok
    But when selecting another number which is not in the database I got all data !!
    I want when I select a nother number which is not in the database to get null results (empty cells in my result sheet)
    Attached Files Attached Files

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Auto Filter in VBA

    Change the GetStuff routine to this:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Auto Filter in VBA

    Hi Yasser,

    To get filtered cells, you must use .SpecialCells(xlCellTypeVisible) of the range, otherwise you will get the whole range as if nothing is filtered.

    Please Login or Register  to view this content.
    Regards
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Auto Filter in VBA

    Quote Originally Posted by karedog View Post
    To get filtered cells, you must use .SpecialCells(xlCellTypeVisible) of the range, otherwise you will get the whole range as if nothing is filtered.
    That is only true if all the cells in that range are hidden by the filter. Otherwise, only visible cells are copied in any current version of Excel.

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Auto Filter in VBA

    Quote Originally Posted by romperstomper View Post
    That is only true if all the cells in that range are hidden by the filter. Otherwise, only visible cells are copied in any current version of Excel.
    Back Rory,
    This line of your code will do it.

    Please Login or Register  to view this content.
    Error trapping could only hide the error.
    Last edited by AB33; 11-27-2014 at 01:13 PM.

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Auto Filter in VBA

    Thank you very much for all this help
    you are great people in a great forum

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Auto Filter in VBA

    Quote Originally Posted by romperstomper View Post
    That is only true if all the cells in that range are hidden by the filter. Otherwise, only visible cells are copied in any current version of Excel.
    Thanks for your correction

    Quote Originally Posted by AB33 View Post
    Error trapping could only hide the error.
    Are you really sure about your statement ?

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Auto Filter in VBA

    Quote Originally Posted by karedog View Post
    Thanks for your correction



    Are you really sure about your statement ?
    If I am not sure why should I post a statement-For sentimental reasons?

  9. #9
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Auto Filter in VBA

    Quote Originally Posted by AB33 View Post
    If I am not sure why should I post a statement-For sentimental reasons?
    So it is sentimental reason, OMG you really break my heart

    Please Login or Register  to view this content.
    Sub1 is without error trapping, when run, it stop at line "B = 1 / (1 - A)" and not to continue

    Sub2 is with error trapping, when run, an error is encountered, and Error object's value will be altered, and by examining the error number property, the code flow can be changed, so it is not just "hide the error", but something is really changed, that is error object.

    Sub3 is with error trapping, when run, although the error is encountered, it can even alter a new value so the code can continue, so it is not just "hide the error"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Auto-Filter Pivot Table with New Month filter
    By eskyec in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-06-2013, 06:05 PM
  2. Delete Rows hidden by either Auto Filter and Advanced Filter
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-05-2013, 10:29 AM
  3. [SOLVED] Advanced Filter results don't match auto filter
    By WaterWings in forum Excel General
    Replies: 1
    Last Post: 09-13-2012, 05:37 PM
  4. Auto Filter by InputBox - filter by user defined column
    By Originalgoth in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2010, 12:12 AM
  5. Replies: 1
    Last Post: 09-29-2005, 04:05 PM

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