How to filter in a specific range in Excel 2010

Hi nemo66ro,

Have you tried inserting a blank row at row 51? This would tell Excel that the last row of your table is 50.

Hi,

So I'm seeing you have this table of lots of rows. You want to sort the top 50 rows. I'm thinking your lots of rows grow by time or something? Why don't you sort all the rows by what your criteria is first and then by time second. This would let the top 50 move to the top.

Depending on the problem there are lots of ways to accomplish the problem also. If you had a helper row showing the row number you could do an advanced filter to filter to another sheet or place in the same sheet the data in the top 50 rows.

Now - you are asking for a VBA solution, I understand that. My answer is to always suggest the least complicated method. Because you are having problems with the VBA code in a 9 year old versions, I'd suggest posting a sample workbook and let some guru's look at the exact problem and suggest a non-vba answer. BTW - in newer versions of Excel there is more robust features in TABLES of data. In 2003 they called them lists.
Look at http://office.microsoft.com/en-us/ex...010048546.aspx for tables only.
Also did you know that you can view the top 10 or 50 or 100 using Pivot Tables? http://www.contextures.com/excel-piv...ers-top10.html

The information you may want might be handled easily with a non-vba solution.

Sorry for the rant but you want someone to fix you VBA code that doesn't work because you've upgraded your version from 2003 to 2010. A lot of things have changed since then. If you supply the workbook with code we could look at it.

Because you have 2010 you might want to see what is new in it and possibly avoid VBA. Look at http://blogs.office.com/b/microsoft-...xcel-2010.aspx

Hi Marvin,
I have some doubts that your remark could be a common courtsey...

"you want someone to fix you VBA code that doesn'twork because you've upgraded your version from 2003 to 2010"

As I know I have requested some details about an issue (Filter function) and it wasn't addressed to a customer service. On the other hand, we should consider Excel 2010 a modern tool, more performant than 2003 version.
I thought, I have all reasons to fell like a driver in a fancy car but if something goes wrong with a feature is not a pleasure to receive suggestions for bicycle or pedestrian as "the least complicated method". Of course that entire Excel might be handled with any IDE, but I wonder why this function has changed the behaviour in the 2010 version. Normally, an upgrade should mean a changing from good to better...
Shortly, I have no upgrade to do but some colleagues use an application developed in Excel 2003 and in this version a filter works as below :
Capture.GIF
As you will see in attachment if we try to filter by "John" we will have on the Range("A1:A10") 2 cases (the WorkFunction.CountIf is 2...). Now, if we try to run the same code in Excel2010 we will have 4 cases filtered even the count is 2...Practically, instead of a Range is filtered the entire column...
Capture2.GIF
Thank you in advance for any possible explanation.

I agree that it looks like a bug-although I reckon microsoft will say it's a feature-but I can't really see why it causes you a problem in code since you know what range you want to process anyway?

Hi -

I guess this is an unhappy OP where things changed in 2010 Excel when it worked like he wanted in 2003. I'm not exactly sure but I believe the answer lies with new features to Excel Tables in 2010. See - http://www.databison.com/index.php/t...ulas-in-excel/

This question revolves around having an AUTOFILTER established in a table and wanting it to act on only a portion of the table. I guess with the new features of 2010 tables the assumption was an Autofilter then should apply to the entire table. If the user didn't want the entire table filtered they shouldn't use the autofilter button!??? I don't consider this a downgrade of an excel function but a simple more specific definition of what autofilters do and what assumptions they carry with them.

but this is not a table. it's just a range. if it were a table I'd agree