AUto Filter List 1

[Luis939]

Is it possible, that when you 'autofilter' a list, that somehow you can get each individual item in the list. For example, if i have a column full of Last Names, i want each different name, sort of what it looks like when you use the drop down menu to filter. thanks

 

[VBAjedi]

It is fairly easy to get a list of all the unique values in a column. Copy the whole column to a blank sheet (only necessary if either adjacent column contains data), select it, and apply the Advanced Filter. Don't specify a criteria range, but do select the "Unique Values" checkbox.

The whole process only takes about 30 seconds.

Hope that's what you are after!

VBAjedi

 

[Luis939]

unfortunately i dont have that option in the drop down list....and even if i did...if i use something like activecell.offset(1,0) wouldnt that select the next cell down, regardless of what's on the screen

 

[VBAjedi]

Note that in my post I said to use the Advanced Filter, not the AutoFilter. You are correct in saying that the AutoFilter does not have an "Unique Values" option. The Advanced Filter, on the other hand, does.

And yes, you will have problems if you use the Offset function with either of the filters. The instructions I gave you were just for getting a list of all the unique values in a column, which is how I read your initial post.

If your situation is different or more complex than that, please describe exactly what you are trying to accomplish (including the big picture) and exactly what you currently have. If we have more detailed information, we can help you more accurately.



VBAjedi

 

[Luis939]

My Apologies VBAjedi, everything worked out fine, but one more question, is there anyway to sort of perform the autofilter just in code as opposed to actually doing it on the spreadsheet, so if i just want to work with the values associated with the values in one column

 

[VBAjedi]

It is certainly possible to work with the filters via code, but I must warn you: they can occasionally be frustrating to figure out. Here's some code I use to manipulate the AutoFilter:

' RETURN ONLY FILTERED ROWS
Dim myRange As Range
Selection.AutoFilter ' resets filter
Selection.AutoFilter Field:=1, Criteria1:="Best"
Set myRange = Selection.CurrentRegion.SpecialCells(xlCellTypeVisible)
myRange.Copy
Range("G13").Select
ActiveSheet.Paste

Some notes: if you add VisibleDropDown:=False to the VB code it hides the dropdowns. You will run into problems if you try to AutoFilter a range that already has filters set, so you should check for active filters first (one of the tricky bits - I have some code somewhere that I'll try to find).

Other than that, post back with specific issues you run into (but search the forum first - this stuff has been discussed several times in the past).




VBAjedi

 

[Luis939]

ok thank you very much, ill probably keep it safe and not tamper too much with it, but it's always cool to know more stuff

 

[neiljabba]

Hi VBA

Just reading this post to debug an autofilter issue and noticed that you said you had code for checking an autofilter has been applied or already applied could you possibly post this as I believe this is what I need for my bug.

Many thanks in advance

Cheers

Neil

 

[VBAjedi]

Easiest way is to check the .FilterMode property of your worksheet. To quote the help file, "This property is True if the worksheet contains a filtered list in which there are hidden rows.". It's often used like:

If Worksheet("Sheet1").FilterMode = True Then
   ' Do something
End If

Note that this will return False if the Autofilter is turned on but the user has not selected any criteria yet.

Hope that helps!

VBAjedi