• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

AdvancedFilter in Table not pulling in all of the available rows

John Price

New Member
I have a macro that copies a column from a table and pastes it into another new sheet that has been created. This table has 460 rows of data (not always but for this example, yes) When I copy the column data over to the new sheet sometimes it will copy all 460 rows and sometimes it will only copy the first 42 rows. Nothing changes in the table. I noticed if I step into the code 90% of the time it will copy all 460 rows. If I run it (without stepping into it) 90% of the time it will copy only 42 rows of data from the table. I can't post the code here as it has confidential stuff in it. But I think I narrowed down the issue to a single line of code and wanted to see if anyone else here might know why it is doing this and can provide a solid solution.
My_Range.Columns(FieldNum).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Sheet1").Range("A3"), Unique:=True
My_Range is a declared as a Range where it sets the Range of the Table by using a function called LastRow where it uses cell A2 the first header of the Table and then finds the LastRow of the sheet. Set My_Range = ws4.Range("A2:O" & LastRow(ActiveSheet))
I have not found there to be any errors when at this stage so I have ruled this out already.
FieldNum is a string set as 2 which selects the second column in the table. Just to reiterate this is the line that seems to not be working correctly.
My_Range.Columns(FieldNum).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Sheet1").Range("A3"), Unique:=True
I know I could probably use a better way of declaring the table range using the table itself but I wrote this code a long time ago and instead of making a bunch of changes I'd rather make a simple modification if its possible.


Excel Ninja
I assume there's criteria range set up... In general, AdvancedFilter should only be used when you want to copy subset of data.
Also, by using Unique:=True, if there are duplicate values in a given range, it will only keep one of them.

What you really should do is...
My_Range.Columns(FieldNum).Copy Sheets("Sheet1").Range("A3")
If you want to remove duplicates, you can always apply Remove Duplicates operation on the result (use macro recorder to get base syntax).

Marc L

Excel Ninja
As AdvancedFilter method never fails so check if the source range is correct​
and if yes just add Empty as Criteria range of this method, thinking the same as Chihiro …​