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?


    Code
    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


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

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: VBA Advanced filter


    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.

  • 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



    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


    Quote from Walthobum;794438

    Also in the row above the headers I have numbers that I use for calculations. Could that be causing a problem somewhere?


    Yes, it could because you are using CurrentRegion. Since there is no gap between the data and the calculations, CurrentRegion will include that row and therefore use it as the header row.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

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


    Code
    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


    What exactly does "bugging out" mean?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: VBA Advanced filter


    Apologies again. The sub routine crashes on this line.

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!