Limiting the range of AutoFilter?

[CDiddy]

All,

Thanks in advance for taking a look at my problem. I appreciate it.

I'm trying to figure out how I limit the range that Autofilter
searches. Basically, I have approximately 1000 rows of data, then a
blank row, then a bunch more data beneath. I need to make it, so that
when I click on the arrow, and autofilter for 'top 10', it only gives
me the top 10 results in the upper range of data (the 1000 top rows),
and not the data that is beyond those first 1000 rows.

Is this possible using autofilter? I know I could do with the advanced
filter, but this workbook is used by people who need it to be as simple
as possible.

Thanks for your help.

C

 

[Guest]

Instead of selecting the entire columns when setting up the AutoFilter, just
select the cells that you want to be affected. So, highlight from Row 1 down
through Row 1000.

HTH,
Elkar

 

[CDiddy]

Hi Elkar,

I have tried that, but it still pulls from the entire column.

Any other thoughts? Thanks for the idea.

 

[Arvi Laanemets]

Hi

Add a column into your table, where a group identificator is calculated.
P.e., when in column A always exist a value, when the row is not empty, then
into some column (X) ento row2 enter the formula :
=IF(A2="","",INT(ROW()/1000)+1)
, or
=IF(A2="","",LEFT(UPPER(A2))

Now you at first set autofilter to group column (the second formula allows
to group records alfabetically), and then filter the result for 'top 10'.
And avoid empty rows - otherwise you have to define the autofilter range
manually every time.

Arvi Laanemets

 

[Guest]

Hmm... that's strange, I tested it and it works fine for me. Are you sure
that row 1001 is completely blank?

Maybe try turning off AutoFilter, then save your spreadsheet, then close.
Open again and reapply AutoFilter.

What version of Excel are you using? Excel 2003 is the only one I have
access to at the moment, so perhaps AutoFilter behaves differently on older
versions? Other than that, I'm not sure why it isn't working for you.

 

[Guest]

I've had similar problem limiting the rows to filter.

I found by chance this workaround:
Name the rows or range of cells to filter (insert, name, cells, range)
Select the range that you just created in the range name box to left of
formula bar
( I also clicked/highlighted the range name but not sure if this is
significant)
now create auto filter
Save the worksheet
Now use autofilter dropdown list

I also found that it takes a few blank rows after the range list before the
next non- blank line. This was due to my using a lot of blank cells and lines
in the range I think.

Hope this helps...........

SWURST