跳过动态AdvancedFilter CriteriaRange中的空白单元格
跳过动态AdvancedFilter CriteriaRange中的空白单元格
提问于 2022-03-30 13:11:14
如何跳过/忽略CriteriaRange (AdvancedFilter)中的空白单元格？
Sub BrandExtraction ()
Application.CutCopyMode = False
Dim rngCrit As Range
Dim rngData As Range
Set rngData = Sheets("ProductPriceExport").Range("A1").CurrentRegion
With Sheets("Campaign")
Set rngCrit = .Range("C1", .Range("C" & Rows.Count).End(xlUp))
End With
rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=Range("A1:AN1"), Unique:=False我找到了一个选项，如果它是一个桌子区的话，可以对竞选表(Colum B)进行排序。
我尝试了ActiveSheet.ListObjects函数("Table1")。ListColumns (2) .DataBodyRange.Select，但它仍在从ProductPriceExport文件复制整个数据集。
Sub PrimaryBrandExtractionTestTable()
Application.CutCopyMode = False
Dim rngCrit As Range
Dim rngData As Range
Dim tbl As ListObject
**Set tbl = ActiveSheet.ListObjects("KampagneTabel")**
Set rngData = Sheets("ProductPriceExport").Range("A1").CurrentRegion
With Sheets("Campaign")
Set rngCrit = **tbl.ListColumns(2).DataBodyRange.Select**
End With
rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=Sheets("BrandExtraction").Range("A1:AN1"), Unique:=False
End Sub回答 2
Stack Overflow用户
发布于 2022-03-31 07:32:42
使用高级过滤器(在AutoFilter的帮助下)
AutoFilter.
- The第二个解决方案使用
AutoFilter删除复制的‘空白’。
Option Explicit
Sub BrandExtractionBasic()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim rngData As Range
Set rngData = wb.Worksheets("ProductPriceExport").Range("A1").CurrentRegion
Dim rngCrit As Range
With wb.Worksheets("Campaign")
Set rngCrit = .Range("C1", .Range("C" & .Rows.Count).End(xlUp))
End With
Dim rngCopy As Range
With wb.Worksheets("BrandExtraction")
.UsedRange.Clear
Set rngCopy = .Range("A1").Resize(, rngData.Columns.Count)
End With
rngData.AdvancedFilter xlFilterCopy, rngCrit, rngCopy
End Sub
Sub BrandExtraction()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim rngData As Range
Set rngData = wb.Worksheets("ProductPriceExport").Range("A1").CurrentRegion
Dim rngCrit As Range
With wb.Worksheets("Campaign")
Set rngCrit = .Range("C1", .Range("C" & .Rows.Count).End(xlUp))
End With
With wb.Worksheets("BrandExtraction")
.UsedRange.Clear
Dim rngCopy As Range
Set rngCopy = .Range("A1").Resize(, rngData.Columns.Count)
rngData.AdvancedFilter xlFilterCopy, rngCrit, rngCopy
Set rngCopy = .Range("A1").CurrentRegion ' reusing variable!
With rngCopy
Set rngData = .Resize(.Rows.Count - 1).Offset(1) ' reusing variable!
.AutoFilter 9, "=" ' filter blanks ('9' means 'I' column)
End With
Dim rngVisible As Range
On Error Resume Next
Set rngVisible = rngData.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilterMode = False
If Not rngVisible Is Nothing Then rngVisible.Delete xlShiftUp
End With
End SubStack Overflow用户
发布于 2022-03-30 13:28:12
你可以试试这个：
CriteriaRange:=Array(rngCrit, "<>")我还没试过
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71677870
复制相关文章
相似问题
腾讯云开发者
