高级过滤器-排除报头

Question

我有一个宏做一个高级过滤器。我怎么能排除头从这?我尝试将C:C更改为C2:C，但它不起作用。

Sub extractuniquevalues2()

Dim wks As Excel.Worksheet
Dim wksSummary As Excel.Worksheet
'----------------------------------------------------------------------------------
'edited so it shows in the 3rd column row +1.  Add the header and sheet name macro to this
On Error Resume Next
Set wksSummary = Excel.ThisWorkbook.Worksheets("Unique data")
On Error GoTo 0
If wksSummary Is Nothing Then
    Set wksSummary = Excel.ThisWorkbook.Worksheets.Add
    wksSummary.Name = "Unique data"
End If

'Iterate through all the worksheets, but skip [Summary] worksheet.
For Each wks In Excel.ActiveWorkbook.Worksheets
    With wksSummary
        If wks.Name <> .Name Then
            If Application.WorksheetFunction.CountA(wks.Range("C:C")) Then
                Call wks.Range("C:C").AdvancedFilter(xlFilterCopy, , .Cells(.Cells(.Rows.Count, 3).End(xlUp).Row + 1, 3), True)
            End If
        End If
    End With
Next wks

    End Sub

让你看看我的图片:[img]https://i.stack.imgur.com/On3Gh.jpg

希望去掉标题，并使名称逐行排列，中间没有空格。

------------------------------------------------------------------------

所以我是这样做的，得到错误:

    Sub testage()

Dim wks As Excel.Worksheet
Dim wksSummary As Excel.Worksheet
'----------------------------------------------------------------------------------
'edited so it shows in the 3rd column row +1.  Add the header and sheet name macro to this
On Error Resume Next
Set wksSummary = Excel.ThisWorkbook.Worksheets("Unique data")
On Error GoTo 0
If wksSummary Is Nothing Then
    Set wksSummary = Excel.ThisWorkbook.Worksheets.Add
    wksSummary.Name = "Unique data"
End If

'Iterate through all the worksheets, but skip [Summary] worksheet.
For Each wks In Excel.ActiveWorkbook.Worksheets

    Dim r As Range
    ' Get the first cell of our destination range...
       Set r = .Cells(.Cells(.Rows.Count, 3).End(xlUp).Row + 1, 3)
         ' Perform the unique copy...
           wks.Range("C:C").AdvancedFilter xlFilterCopy, , r, True
    'Remove the first cell at the destination range...
           r.Delete xlShiftUp

Next wks

   End Sub

Answer 1

遗憾的是，没有。所有的过滤函数都可以处理标头，甚至包括复制到新目的地的标头，比如您的例子。但是您可以使用Delete xlShiftUp来删除目标范围内的第一个单元格，并将所有内容向上移动一个位置:

Dim r As Range
' Get the first cell of our destination range...
Set r = .Cells(.Cells(.Rows.Count, 3).End(xlUp).Row + 1, 3)
' Perform the unique copy...
wks.Range("C:C").AdvancedFilter xlFilterCopy, , r, True
' Remove the first cell at the destination range...
r.Delete xlShiftUp