Advanced autofilter

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
Evening all,

I've had problems with this time after time trying to copy unique values to a new location on the same page

is there a limit to how many rows it will look at ?

my particular problem occurs on a range of data 8 columns wide by approx 3,000 rows deep - it seems to not bring back the last 800 unique records and I can't understand why

many thanks
Chris
:)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I think so, yeah, thanks Jack....

select the range, select copy to new location, pick unique values and go

it brings back loads, but not all - maybe missing around 800 of wht I'd expect....

I only have problems on this single data range, same last month too (VAT returns!)

some rows are column-filled, ie all 6 columns have entries, whilst some rows have only 4 or 5 column entries, if that mkes a difference...
 
Upvote 0
Chris not the best but easiest you filter so you MUST be able to sort and find say Chris in Col A and copy down as needed, and re sort back as was, excl often messes about here.

or your need VBA to *find* copy row and paste to XXX .

Notice find.... i am for ever shouting to guys find, worth invertigating
 
Upvote 0
thanks for keeping this open Jack... much appreciated !

In the end, I just ditched the method and used a pivot table in its own sheet instead.

ta
Chris
:)
 
Upvote 0
On 2002-04-20 04:59, Chris Davison wrote:
thanks for keeping this open Jack... much appreciated !

In the end, I just ditched the method and used a pivot table in its own sheet instead.

ta
Chris
/board/images/smiles/icon_smile.gif

Just curious: Is the cell from where you activate Advanced Filter in the largest sized column in your data area, if there are any size differences between columns of interest at all?

Aladin
 
Upvote 0
hmmmm

I think I was just in any random cell when I started the autofilter :eek: Have I wrongly assumed it didn't matter ?

also, the columns all ended on row 5,000 but some of them had gaps in various cells (ie processing errors where the inputter had forgotten to put the account code, or another row where the tax code had been forgotten etc etc)
 
Upvote 0
On 2002-04-20 05:23, Chris Davison wrote:
hmmmm

I think I was just in any random cell when I started the autofilter /board/images/smiles/icon_eek.gif Have I wrongly assumed it didn't matter ?

also, the columns all ended on row 5,000 but some of them had gaps in various cells (ie processing errors where the inputter had forgotten to put the account code, or another row where the tax code had been forgotten etc etc)

I thought so :cool:.

I'd suggest creating a dynamic name range based on a most complete (preferably, numeric type of) column, and use that name as List range in Advanced Filter.

Aladin
 
Upvote 0
On 2002-04-11 13:59, Chris Davison wrote:
Evening all,

I've had problems with this time after time trying to copy unique values to a new location on the same page

is there a limit to how many rows it will look at ?

my particular problem occurs on a range of data 8 columns wide by approx 3,000 rows deep - it seems to not bring back the last 800 unique records and I can't understand why

many thanks
Chris
:)

Chris, as I see it you have 1 of 2 problems depending on what you mean by "it seems to not bring back the last 800 unique records".

1. If you mean that these values are missing from the AutoFilter dropdown list, please understand that this list will only contain the 1st 1,000 unique values.

2. If you mean that these values are missing from your extract, you may have a hidden empty row in your data list. You can check for this row by selecting a single cell as before and choosing the Edit | Go To... Special menu command, picking the "Current region" option, and pressing the Shift+Tab key combination. The row beneath the active cell is empty. That row is the demarcation of your list.
This message was edited by Mark W. on 2002-04-20 13:31
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top