VBA Advanced filter

I'm trying to create a unique list from a column in a dataset. The following piece of code has run successfully, (with alterations where necessary for changes in the range, column required etc.), but now doesn't return any entries at range EB6. There definitely is data in the column 9 that I think should be returned. Can anyone see where I'm going wrong?

With Sheets("Data")
    .Range("EB1").EntireColumn.Delete
    .Range("A6").CurrentRegion.Columns(9).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet4.Range( _
       "EB6"), Unique:=True
       
Set rRng = .Range(.Cells(2, 132), .Cells(.Rows.Count, 132).End(xlUp))
End With

Many thanks,
K

Re: VBA Advanced filter

Quote from rory;794413

Hard to be sure without the workbook. Is Sheet4 the codename for the Data sheet?

Apologies. Sheet4 is the codename for the "Data" sheet. I'm just going to check a few things now to make sure it's not something really daft. Checking that I have unique header names. Also in the row above the headers I have numbers that I use for calculations. Could that be causing a problem somewhere? I might change the excel "Data" sheet to leave a row between these "calculation" numbers an the row with the headers in.

Re: VBA Advanced filter

Quote from sktneer;794417

Should work fine.
Remember code is writing the data on Sheet4 (code name) starting from EB6. Is that correct?
Also shouldn't

Code

.Range("EB1").EntireColumn.Delete

be this

Code

Sheet4.Range("EB1").EntireColumn.Delete

As I assume you are deleting the column EB before writing new values in there. Right?
If that is the case, Sheet4.Columns("EB").Clear would be enough to achieve that.

Display More

Yes, that is the sheet I want the unique entries to be pasted to. Although the issue is that no entries are being pasted anywhere by the look of it. Also the second point you raise hasn't been a problem when I have used this technique in the past, but if I get nowhere with the checks I detailed in the post above, I'll give that a try.

Thanks for your thoughts.

Re: VBA Advanced filter

OK, so I have got past this issue now. I think it was due to the way I had set up my "Data" sheet in Excel - i.e. the "calculation" numbers in the row above my header row was causing an issue.

Thanks for your help.

Re: VBA Advanced filter

Hopefully I won't have to start another thread as it's more or less the same issue, advanced filters.

This line of code is bugging out now and, again, it has always worked perfectly in the past. Any thoughts? I've checked the variables are declared and defined correctly.

With Sheets("Data")
    .Range("DZ2").Value = rCl.Value
    .Range("A6").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
                    CriteriaRange:=.Range("DZ1:DZ2"), CopyToRange:=Sheets("Activity Data").Range("A6"), Unique:=False

Re: VBA Advanced filter

Apologies again. The sub routine crashes on this line.

.Range("A6").CurrentRegion.Columns(9).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet4.Range( _ 
    "EB6"), Unique:=[COLOR=blue]True[/COLOR]