Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter for numbers (Excel 2000)

    Your PS is what is important: formatting all cells in the range as text should do the trick.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofilter for numbers (Excel 2000)

    I have a sheet with alphanumeric codes in column A. These are 4 characters long, the first character could be alpha, the rest are numeric. I'm trying to apply a custom autofilter, using "begins with" applied to the first character. For instance, to show only rows where the code starts with a "D", or only rows where the code starts with a "4". This will work OK if the codes of interest begin with an alpha character, but won't work for codes starting with numeric values.

    I want to ultimately incorporate this into VBA, so coding it up would be OK. Is there any workaround for this behaviour?

    thanks

    Alan

    P.S. Cells are formatted as "General", if that's of any relevance.
    A recorded macro shows:
    Selection.AutoFilter Field:=1, Criteria1:="=4*", Operator:=xlAnd
    this doesn't work, but "=D*" does work, so I'm wondering if I need to format cells as text perhaps?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Autofilter for numbers (Excel 2000)

    Alan:

    A simple work around would be to open a new column A thereby making the alphanumeric column to B.
    In column A use the following formula = Left (B1,1). Then copy down the formula to every entry to be filtered.
    Lastly redo the autofilter to include the new column A. Do your autofilter from column A amounts.

    This should work.

    Regards,

    TD

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter for numbers (Excel 2000)

    I had a further play with this and found that the formatting had no effect. But in the course of playing about, I got to thinking about ASCII character values and bingo! I can use an AND criteria to filter for >= 4000 AND <5000.

    Similarly, I can use >=D AND <E. This should fit in nicely with the rest of the VBA, which uses chr() and asc() to manipulate the various code groups. So simple it's scarey <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    Alan

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter for numbers (Excel 2000)

    Thanks for your solution TD. And yes, it seems to work as advertised. I found a very suitable method for my particular situation, but I'll log yours for future reference.

    cheers

    Alan

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter for numbers (Excel 2000)

    Going a bit deeper with this, I'm wondering how to return the range of rows that an autofiltering operation makes visible on the spreadsheet. Following on from the example I gave, if I've successfully displayed everything in column C that begins with a "7" then XL displays rows 68 to 122, showing a blue highlight on those row numbers at the left. How do I get hold of this range (C68:C122) in my VBA code?

    All of this auto and advanced filtering is new ground for me in VBA, and I don't find the help file very helpful. Any links to good tutorials/ examples on the web?

    thanks

    Alan

    P.S. Is it advisable/ necessary to have a "header" row for the sheet or range that is being filtered?

  7. #7
    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: Autofilter for numbers (Excel 2000)

    XL will use the top row as a header so you should have one as it will not filter
    The range of visible items is given by:

    activesheet.autofilter.range.specialcells(xlcellty pevisible)

    You can select it:
    activesheet.autofilter.range.specialcells(xlcellty pevisible).select

    Get the address:
    sAddress = activesheet.autofilter.range.specialcells(xlcellty pevisible).address

    Copy it:
    activesheet.autofilter.range.specialcells(xlcellty pevisible).copy

    Etc etc, it is a range object

    Here is some info.John Walkenbach has code to display the criteria.

    These should get you started.

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter for numbers (Excel 2000)

    Thanks Steve. It was the specialcells(xlcelltypevisible) that was eluding me. In fact, I was using the range expression you give, without this qualifier, and was beginning to wonder if autofilters were of any use at all <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. Once I "twigged" to that I got a bit of a roll started. I finally did hit on the code by Tom Ogilvy that you cited, which helped immensely with one of the tasks I was needing. Another is to be able to display a list of unique values in one particular column and access the rows in that range. I'll continue the hunt...

    cheers

    Alan

  9. #9
    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: Autofilter for numbers (Excel 2000)

    Try the code on John Walkenbach's site it fills a list box with the unique items (sorted) in a range.
    [The "trick is to create a collection and use the items as the "index" which must be unique.]

    Once you have the list, you could have code after selecting to select the rows that match or do whatever you want.

    Steve

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter for numbers (Excel 2000)

    A Listbox - this is exactly what I was wanting to do with the unique entries! I had found code here at the same site, to produce an array, which I had destined for a listbox. This clever trick you found cuts out the middleman. Many thanks Steve... just what the doctor ordered.

    I had hoped to use the Advanced Filter function originally, but it seems that the CopyToRange argument is the only way to "obtain" the results. I would have liked to be able to access a resulting (existing) set of row values, but I guess there's really nothing to say which row might be singled out as the "unique" one for each set of duplicate values. This inbuilt function is more likely to use an internal array to store the results as it processes I'd guess (rather than building up a range inventory). Does that sound right?

    Alan

  11. #11
    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: Autofilter for numbers (Excel 2000)

    That is how I imagine the technique works. Advanced filter will internally extract, get out the unique items and then dump the list. I also think this is why we have no access to the "autofilter" list. I don't think it is stored anywhere. it think it is generated and displayed when the button is pressed and not stored.

    A tip to improve the speed of the code I referenced. The code sets the range of interest and reads this range into the collection. It will probably be faster to read the range into an array first (dim as variant)

    Dim vArray
    Something like:
    dim x as long
    vArray = Range("A1:A105").value

    for x = 1to ubound(varray)
    NoDupes.Add varray(x,1), CStr(varray(x,1))
    next

    Reading directly into an array is quicker than individually reading the cells. Once in a array it is easier to go thru them. Arrays read from ranges are always 2 dimensional [hence the varray(x,1)]. They are essentially rows x columns and you have one column.

    Steve

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter for numbers (Excel 2000)

    Steve,

    I have been using the code you cited with some success, but I have also noticed some sluggish performance. I haven't known what has been the cause though because the train of events is long & varied. My form involves generating up to 4 comboboxes, each one "feeding off" the choice made in the previous one. It's smooth enough when it first fires, but tends to crawl as selections keep being changed willy nilly.

    I did like the idea of a collection (aesthetic I suppose) but I see your point about reading in one large range as opposed to lots of little ones. I guess it's not possible to "dump" a range into a collection the same way as you can to an array. The "oddest" thing I noticed, which I never realised, was that the mere act of filling the list was actually firing the change event for the box, without anything happening at the user interface. This proved to be a heck of an annoyance, since the next box in the chain was trying to fill after each item was added. The only way I could think to stem this was to keep all boxes invisible until they're filled and use:
    If ComboBoxN.Visible = False Then Exit Sub
    as the first statement in each ComboBoxN change event. Maybe the enabled property could be used in this capacity. Are there any less "clumsy" ways of stopping the change event from firing?

    Alan

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

    Re: Autofilter for numbers (Excel 2000)

    You could use a global Boolean variable StopEvent. By default, this will be false. At the beginning of the code to populate the combo box, set StopEvent = True, and at the end, reset it: StopEvent = False.
    In the On Change event of the combo box, test StopEvent: If StopEvent Then Exit Sub

  14. #14
    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: Autofilter for numbers (Excel 2000)

    Hans answered you primary question. About collections, the only reason for the collection is to make the unique list from the key. This must be done item by item. If you want to go thru the array at different times to do different things, it is best to do all the items you want to do as you go thru the list.

    If this is not a problem, you can just use arrays which can be used to read/write to ranges in one fell swoop.

    Any manipulation of the arrays in any "part: (to get subsets, for example) must be done item by item (though this is relatively fast in VB)

    Steve

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter for numbers (Excel 2000)

    Thanks Hans. Neat and clean as always. Your method doesn't interfere with/ rely on any other ascect of the code; unlike mine, which dictates/ relies on the visibility of the comboboxes. In my case, I want to keep the boxes invisible until they are "ready for use" anyway, but this wouldn't be a generally acceptable restriction.

    Alan

Page 1 of 2 12 LastLast

Posting Permissions

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