VBA Advanced过滤唯一值并复制到另一张纸上

问题描述

我尝试了多种方法来过滤一列唯一值(包括空白和内部有空格的空白).我想过滤这些值，然后将它们粘贴到另一张纸的a列中.

I've tried a number of methods to filter a column for unique values (including blanks and blanks with a space inside). I want to filter these values and then paste them into column a in another sheet.

Sub filter_2()
    With Sheets("ICM flags")

        .Columns("a").AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets("Flag Update (2)").Range("a2"), Unique:=True

    End With
End Sub

以上方法适用于.range("a2")，但如果尝试使用.range("a1")，则会收到运行时错误1004类的高级过滤器方法失败.

The method above works for .range("a2") but if I try to use .range("a1") I get runtime error 1004 advanced filter method of class failed.

我收到类的运行时错误1004高级过滤器方法失败，下面的方法.

I get runtime error 1004 advanced filter method of class failed with the method below.

Sub unique_filter()

               Sheets("ICM flags").Columns("a").AdvancedFilter _
                     Action:=xlFilterCopy, _
                        copytorange:=Sheets("Flag Update (2)").Range("a1"), Unique:=True

End Sub

对于上述两种方法以及对Davesexcel的编辑，答案:

For both of the above methods and with the edits to Davesexcel answer:

Sub AdvFilter()
Dim ws As Worksheet, sh As Worksheet
Dim rng As Range, Frng As Range

Set sh = Sheets("ICM Flags")
Set Frng = sh.Range("A:A")
Set ws = Sheets("Flag Update (2)")
Set rng = ws.Range("A1")

'Frng.AdvancedFilter Action:=xlFilterCopy, copytorange:=rng, Unique:=True
Frng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

End Sub

该方法适用于FilterInPlace，但不适用于CopyToRange

The method works for FilterInPlace but not CopyToRange

推荐答案

似乎是由于尝试使用CopyToRange复制到另一张纸上而引起的.我已通过以下方法解决此问题:首先复制到同一张纸上，然后将这些值复制到我想要它们的纸上

It seems the problem was being caused by trying to use CopyToRange to copy to another sheet. I have worked around this by first copying to the same sheet then copying these values to the sheet I want them on

Sub AdvFilter() 'filters flags copied from ICM to unique values and pastes  into Flag update sheet.

    Dim ws As Worksheet, sh As Worksheet
    Dim rng As Range, Frng As Range, Prng As Range

    Set ws = Sheets("Flag Update (2)")

    Set sh = Sheets("ICM Flags 1")'destination sheet
    Set Frng = sh.Range("A:A")'filter range
    Set rng = sh.Range("c1")'filter output range


    Frng.AdvancedFilter Action:=xlFilterCopy, copytorange:=rng, Unique:=True
    ws.Columns("a").Value = sh.Columns("c").Value 'Separate copying as the filter didn't like copying to another sheet

End Sub

这篇关于VBA Advanced过滤唯一值并复制到另一张纸上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！