Chris not to sound off but you are copying the selection ?
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
Free Excel based Web Toolbar available here.
Jack in the UK
J & R Excel Solutions
"making Excel work for you"
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...
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
Free Excel based Web Toolbar available here.
Jack in the UK
J & R Excel Solutions
"making Excel work for you"
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
![]()
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?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]
Aladin
hmmmm
I think I was just in any random cell when I started the autofilterHave 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 soOn 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'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
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".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
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 ]
Like this thread? Share it with others