+ Reply to Thread
Results 1 to 12 of 12

Can I use a Range Name in Autofilter - what would be the correct vba text?

  1. #1
    Registered User
    Join Date
    01-17-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, & 2010 Beta
    Posts
    78

    Can I use a Range Name in Autofilter - what would be the correct vba text?

    If I want to filter for a value in some set of data, I can do Autofilter & pick the particular value, say 39, from the drop-down list of numbers present. If I record a macro that does this, its vba text will stay with the particular number, 39. Unless I can edit that macro, in the right way, this will confine said macro to only seeking the value 39 & nothing more. I have tried to edit the vba text so that the 'criterial' is the value in a specific cell or in a specific range name.
    So far I've not had success - when I run the macro (edited that way), it just throws up all of the values - it does no filtering at all.
    What would be the correct way to edit my macro line, does anyone know?
    Examples:
    Please Login or Register  to view this content.
    - this line bring about no filtering.
    whereas
    Please Login or Register  to view this content.
    - this works but is obviously 'too specific'.
    Thanks for any help.
    LotusMan

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Can I use a Range Name in Autofilter - what would be the correct vba text?

    Your 1st code should work & will filter on whats in C2, but you will need to redo the filter if the value changes.

  3. #3
    Registered User
    Join Date
    01-17-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, & 2010 Beta
    Posts
    78

    Re: Can I use a Range Name in Autofilter - what would be the correct vba text?

    Thing is, it doesn't. It 'throws up' all of the data, filtering out nothing. If it did work, I planned to combine some macros to do, say, 5 or 6 different filtering jobs, and copy/paste the results to separate areas. But it does not filter the data, which is what's been so puzzling.

  4. #4
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Can I use a Range Name in Autofilter - what would be the correct vba text?

    What kind of data ? Give us an example.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Can I use a Range Name in Autofilter - what would be the correct vba text?

    Hey lotusman,

    The criteria range needs to be two rows not just one like your code shows. The top row is the header names of your data and the second (or more rows) need to be how you want to filter the data.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Can I use a Range Name in Autofilter - what would be the correct vba text?

    Thing is, it doesn't. It 'throws up' all of the data, filtering out nothing.
    In that case can you please upload a sample file?

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Can I use a Range Name in Autofilter - what would be the correct vba text?

    @MarvinP, The header would only be needed if the AdvanceFilter method was being used. Autofilter includes a parameter for the field to filter.

    As suggested post example file so we can see data in field 5 and C2.
    Cheers
    Andy
    www.andypope.info

  8. #8
    Registered User
    Join Date
    01-17-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, & 2010 Beta
    Posts
    78

    Re: Can I use a Range Name in Autofilter - what would be the correct vba text?

    I'm attaching the file here - not very big. The macro runs 6 times, on 6 different criteria, and copies results over to the right. I've added a text box with pointers. Thanks for the replies, already, and any further help.
    Lotusman
    Attached Files Attached Files

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Can I use a Range Name in Autofilter - what would be the correct vba text?

    Try formatting the value so it matches with the number format being filtered.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    01-17-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, & 2010 Beta
    Posts
    78

    Re: Can I use a Range Name in Autofilter - what would be the correct vba text?

    Andy,
    You're a genius! It worked, it did the filtering as it was meant to. When inputting the numbers required to vba, I was keying them in with the same formatting, but I see, now, that range names or cells need that extra 'qualification'...
    Thank you very much indeed!
    Lotusman

  11. #11
    Registered User
    Join Date
    01-17-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, & 2010 Beta
    Posts
    78

    Re: Can I use a Range Name in Autofilter - what would be the correct vba text?

    Quick Question, Andy, or to anyone who has the answer: if I want a negative number to appear with parentheses, and not with a minus sign, e.g. -3 would look like (3.0) [and not like -3.0], should I put parentheses around the 0.0 bit, in the code, as in « ......"C2), "(0.0)") ». I think I am getting anomalies where criteria1 is negative, hence my question. Thanks,
    Lotusman

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Can I use a Range Name in Autofilter - what would be the correct vba text?

    Yes the filter criteria will need to be (3.0) rather than -3.

    You can use the Text property of the range, rather than the default Value. Text will use the text displayed in the cell.
    Note that the column width needs to be wide enough to display the value correctly. If the column is too thin then ### will be displayed and that will be the value returned by Text.

    Please Login or Register  to view this content.

+ 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. [SOLVED] Range Address Is Correct Columns Count Is Not Correct
    By goss in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-09-2013, 12:47 PM
  2. Enter text value into next availble cell in range if user selects correct answer
    By jerbaldw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2012, 10:24 AM
  3. Trying to select a range prior to deleting it so i can verify correct range removed
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2011, 08:59 PM
  4. VBA autofilter not picking up the correct wanted information
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2011, 12:24 PM
  5. Selecting correct cell after Autofilter
    By subtilty in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2011, 11:49 AM
  6. AutoFilter method of Range class failed - Yet autofilter works.
    By Carlsbergen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2009, 05:43 PM
  7. Autofilter Lists across Multiple Sheets, Maintain Correct Referenc
    By EDSTAFF in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-14-2005, 11:30 AM

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