Excel AutoFilter

[Guest_imported]

Ive got a fairly large spreadsheet (3200 records) with autofilter on. problem is some of the columns will only display down to say 'M' where as others will display down to 'z' but i think are missing some records out. If i remove say the top 50 and then look in the auto-filter drop down menu i get more available. (the same amount removed i guess)

Is there a limit to the excels capabilities on this or any plug-ins you can suggest? Its either that or drop the lot into access...

running Office 97 sr2b

ta

 

[acron]

There is a limit to teh number of items excel will show in an Autofilter Dropdown list, 1024 or thereabots I think.

You can however use teh Custom option, and enter the criteria there. That should find what you want.

AC

 

[bkpchs237]

sikprob,

The specific limits are referred to in this link:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q105322

Hope this helps.

 

[DaleWatson123321]

ta,

Another DEFINITE alternative you should "explore" is the "next extension" of "AutoFilter". AutoFilter is normally used to "Filter-in-Place". However, this "next extension" is to "Copy to another location" - which you'll find under Data - Filter - Advanced Filter.

The following code is an extraction from a larger application where the user picks from a listbox, with the answer going to "user_choice". Then, based on this user choice, the following Case Statement selects the subroutine that extracts the required data to a separate sheet.

There are advantages to using this alternative instead of Filtering-in-Place. Advantages such as: 1) You can have a separate sheet already set up with the required page formatting. 2) You can include a subroutine that will automatically adjust the print range to match the number of records extracted. 3) You leave the original data untouched. 4) It can be a much cleaner method for the end-user, especially if we are talking about a situation where there are various reports that are "pre-defined".

Sub Get_Choice()
user_choice = Range("rpt_choice&quot.Value
End Sub

Sub Extract_Choice() ' extract data based on user choice
Select Case user_choice
Case 1
Ext_Normal
Case 2
Ext_Revoked
End Select
End Sub

Sub Ext_Normal()
Range("data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="cr_nor", _
CopyToRange:=Range("out&quot, _
Unique:=False
End Sub

Sub Ext_Revoked()
Range("data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="cr_rev", _
CopyToRange:=Range("out&quot, _
Unique:=False
End Sub


I hope you find this example useful.

If anyone would like a copy of an example file which demonstrates the above, please feel free to ask.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca