AutoFilter row limit in Excel 2007?

[Excel_VBA]

Have a spreadsheet with 112,961 rows of data. The first row contains field names, all other rows have data.

The columns are from A to W. The following columns are blank (at least they appear to be): G, L, T, V and W. When I select the AutoFilter, it appears across all columns from A - W.

The problem is that the filter picks up data down to row 102,135. From row 102,136 to 112,961, those rows of data are not being picked up. I verified this by adding values with certain values filtered in and filtered out, then compared to the total of all values unfiltered.

I don't see a blank or hidden row after 102,135. I suppose the answer might be in something different from row 102,135 vs. the rows after it. But I don't see anything that stands out as an anomaly.

Why doesn't my AutoFilter pick up to the last row?

 

[Richard Schollar]

Do any of the rows past this apparent last one contain formulas rather than hardcoded values? If so, are you using SUBTOTAL formulas anywhere?

 

[Excel_VBA]

Do any of the rows past this apparent last one contain formulas rather than hardcoded values? If so, are you using SUBTOTAL formulas anywhere?

There are not any formulas. No Subtotals either.

 

[Richard Schollar]

How are you applying Autofilter? Is it manually thru the Excel interface or via code? If so, please post the code. I take it you have tried simple things like removing Autofilter, saving the workbook down, closing and reopening and applying Autofilter again?

 

[Excel_VBA]

How are you applying Autofilter? Is it manually thru the Excel interface or via code? If so, please post the code. I take it you have tried simple things like removing Autofilter, saving the workbook down, closing and reopening and applying Autofilter again?

I'm applying it manually, no VBA code. Yes to all of the other questions.

 

[Richard Schollar]

Remove Autofilter from this sheet and then, with it still visible in the Excel window, open up the VBE (Alt+F11), go to the Immediate Window (Ctrl+G) and type in the following (and hit Enter at the end):

Range("A1:W112961").Autofilter

Then type in the following and post in your reply what is returned:

?Activesheet.Autofilter.Range.Address

Hopefully it will return A1:W112961. If so, try applying your filters and ensure the full range is filtered.

 

[ShieBoon]

Hello Richard,

I'm also having a slight problem with autofilter. I'm searching for solutions and i came across this thread. I followed your instructions and i get this.

Rows("2:329").autofilter
?Activesheet.Autofilter.Range.Address
$2:$1002

Whenever i run my macro that applies autofilter from row 2 to the last row of data, my row numbers after the last few rows goes like this,

327
328
329
1003
1004
1005

I can't seem to solve it. Can you help?

Thanks,
Shie Boon

 

[Richard Schollar]

You can't have entirely blank rows in 330:1002. Assuming you don't think you need anything that might be there then you can select all these rows and delete them then apply Autofilter again and you should see that the Autofilter range is from 2:329.

 

[ShieBoon]

Okay Richard, i shall try that tmr and give you a reply. Thank you!

 

[ShieBoon]

Hi Richard, i've tried it again and now my autofilter range is 2:329! THANK YOU!!