进阶筛选器-排除标题 [英] Advanced Filter - exclude headers
问题描述
我有一个执行高级过滤器的宏.如何从中排除标题?我尝试将 C:C
更改为 C2:C
,但是它不起作用.
I have a macro that does an Advanced Filter. How can I exclude the headers from this? I tried changing C:C
to C2:C
but it's not working.
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] http://i.imgur.com/xGcAZMj.jpg[/img]
To show you a visual check my image: [img]http://i.imgur.com/xGcAZMj.jpg[/img]
想要摆脱标题,并逐行使用名称,且之间没有空格.
Would like to get rid of the headers and have the names row-by-row without spaces between.
编辑 ------------------------------------------------------------------------
EDIT------------------------------------------------------------------------
所以我这样做,却收到错误消息:
So i'm doing it like so, getting errors:
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
推荐答案
不幸的是,没有.所有过滤器功能都可与标题一起使用,即使您复制到新目的地的标题也是如此,例如您的情况.但是,您只需进行 Delete xlShiftUp
的后续操作,即可删除目标范围内的第一个单元格,并将所有内容移到某个位置:
Unfortunately, no. All of the filter functions work with headers, even the ones where you copy to a new destination, such as in your case. But you can just follow-up with a Delete xlShiftUp
to remove the first cell at your destination range and shift everything up a spot:
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
这篇关于进阶筛选器-排除标题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!