+ Reply to Thread
Results 1 to 12 of 12

Can't apply filter to one column?

  1. 02-17-2018, 11:16 AM #1
    RustyNail
    RustyNail is offline
    Forum Contributor
    Join Date
    03-07-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    274

    Can't apply filter to one column?

    Hello,

    I am trying to apply a filter to one column.

    Display all rows where column A contains an "X"....

    I looked for examples but doesn't work :-(
    http://www.exceltip.com/vba/vba-code...lter-data.html


    Here is what I am trying :

    Please Login or Register  to view this content.
    All help would be appreciated!
    R
    Reply With QuoteRegister To Reply
  2. 02-17-2018, 11:26 AM #2
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Can't apply filter to one column?

    How about...
    Please Login or Register  to view this content.
    Or even...

    Please Login or Register  to view this content.
    Last edited by jeffreybrown; 02-17-2018 at 11:28 AM.
    HTH
    Regards, Jeff
    Reply With QuoteRegister To Reply
  3. 02-17-2018, 11:31 AM #3
    RustyNail
    RustyNail is offline
    Forum Contributor
    Join Date
    03-07-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    274

    Re: Can't apply filter to one column?

    Hi jeffreybrown,

    It works!

    wow.... questions:

    What does: Range("A1", Range("A" & Rows.Count).End(xlUp)) do?

    What does the "1" mean in: .AutoFilter Field:=1, ?

    and what if I want other text filters??

    Thanks so much
    Last edited by RustyNail; 02-17-2018 at 12:35 PM.
    Reply With QuoteRegister To Reply
  4. 02-17-2018, 11:37 AM #4
    kev_
    kev_ is offline
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Can't apply filter to one column?

    another 2 for your collection
    Please Login or Register  to view this content.
    CurrentRegion does not work if you have blank cells in the range
    Last edited by kev_; 02-17-2018 at 11:41 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear
    Reply With QuoteRegister To Reply
  5. 02-17-2018, 11:39 AM #5
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Can't apply filter to one column? 

    Please Login or Register  to view this content.
    This is simply a method to find the last row in column A. Turn on the macro recorder and record your steps using the filter on your column A data set. Turn off the recorder and see what Excel recorded for you in the VBE. If you data set was from A1:A27, the recorded macro would look like...

    Please Login or Register  to view this content.
    Since A27 may not always be the end, we must find the last row.

    The 1 simply means the filter is applied to that column. Let's say the data set was A1:D100 and I was filtering in column D then that number could be 4.
    Reply With QuoteRegister To Reply
  6. 02-17-2018, 12:39 PM #6
    RustyNail
    RustyNail is offline
    Forum Contributor
    Join Date
    03-07-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    274

    Re: Can't apply filter to one column?

    thanks jeffreybrown ,

    Yes I have to start using the macro recorder...

    One last thing,

    what if I want to add more text criteria for example .... as all rows with "X" and all rows with "Qry(x)" ?

    could I do:

    Please Login or Register  to view this content.
    ?


    I tried recording the filter, but the macro recorder gave me this which makes no sense???

    Please Login or Register  to view this content.

    Thanks
    Last edited by jeffreybrown; 02-17-2018 at 01:17 PM. Reason: Please use code tags!
    Reply With QuoteRegister To Reply
  7. 02-17-2018, 12:48 PM #7
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Can't apply filter to one column?

    Probably:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  8. 02-17-2018, 12:57 PM #8
    RustyNail
    RustyNail is offline
    Forum Contributor
    Join Date
    03-07-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    274

    Re: Can't apply filter to one column?

    I want to add more text criteria for example .... as all rows with "X" and all rows with "Qry(x)" ?

    I did it using the macro recorder like this:

    Please Login or Register  to view this content.
    Can someone explain to me why this works?
    Last edited by jeffreybrown; 02-17-2018 at 01:18 PM. Reason: Please use code tags!
    Reply With QuoteRegister To Reply
  9. 02-17-2018, 01:03 PM #9
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Can't apply filter to one column?

    From help:

    The criteria (a string; for example, "101"). Use "=" to find blank fields, or use "<>" to find nonblank fields
    Reply With QuoteRegister To Reply
  10. 02-17-2018, 01:19 PM #10
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Can't apply filter to one column?

    RustyNail,

    Please use code tags when posting code!
    Reply With QuoteRegister To Reply
  11. 02-17-2018, 01:20 PM #11
    RustyNail
    RustyNail is offline
    Forum Contributor
    Join Date
    03-07-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    274

    Re: Can't apply filter to one column?

    thank you xladept
    Reply With QuoteRegister To Reply
  12. 02-17-2018, 01:26 PM #12
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Can't apply filter to one column?

    You're welcome and thanks for the rep!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to apply filter a column
    By Muktar888 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-09-2017, 03:58 PM
  2. [SOLVED] Apply VBA Script based on Text in one column and apply a formula in another column
    By vvincent in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2016, 09:19 PM
  3. [SOLVED] Data into multiple worksheets based on column in Excel and apply auto filter and freeze
    By skumarlingam in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-28-2016, 04:50 PM
  4. [SOLVED] Apply a Number filter with 2 "Between" Ranges on a single column
    By fpperryaz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2016, 07:25 PM
  5. Need to apply filter by contain all text as "mathew" in column "D"
    By aleanboy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-16-2014, 08:47 AM
  6. advanced filter - button to re apply advanced filter across multiple sheets
    By motmac87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 11:16 PM
  7. Apply auto filter for a single column
    By hartford27 in forum Excel General
    Replies: 5
    Last Post: 06-30-2008, 07:31 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1