Advanced autofilter

[Chris Davison]

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

[Jack in the UK]

Chris not to sound off but you are copying the selection ?

[Chris Davison]

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...

[Jack in the UK]

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

[Chris Davison]

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

[Aladin Akyurek]

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
[img]/board/images/smiles/icon_smile.gif[/img]

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

[Chris Davison]

hmmmm

I think I was just in any random cell when I started the autofilter 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)

[Aladin Akyurek]

On 2002-04-20 05:23, Chris Davison wrote:
hmmmm

I think I was just in any random cell when I started the autofilter [img]/board/images/smiles/icon_eek.gif[/img] 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 .

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

[Mark W.]

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 ]