Excel VBA AutoFilter添加空行 [英] Excel VBA AutoFilter adds empty rows

问题描述

我已经修改了我的Excel宏，之前逐行排列，它现在大量过滤结果和副本。更有效率。

我现在遇到的问题是，自动筛选器将数百万个空行添加到工作表中，我无法确定为什么会这样做。 >

CountryCodes是一个包含过滤器值的字典。
条件正在寻找包含字典条目的行。

这是代码：

 对于每个vall在CountryCodes 
 thisWB.Activate 
 thisWB.Sheets（总数）。激活
 
 lookfor = CountryCodes。 item（vall）
 rep =替换（thisWBName，EMEA，lookfor）
 
设置rng = ActiveSheet.Range（A1：Z1）
 
 FilterField = WorksheetFunction.Match（Host，rng.Rows（1），0）
 
如果ActiveSheet.AutoFilterMode = False然后rng.AutoFilter 
 
 rng.AutoFilter字段：= FilterField，Criteria1：== *& lookfor& *，运算符：= xlFilterValues 
 
设置rng2 = ThisWorkbook.Worksheets（总体数字）Cells.SpecialCells（xlCellTypeVisible）
 
 rng2.Copy工作簿（rep） （总数）。范围（A1）
 
工作簿（rep）.Save 
 
 thisWB.Activate 
 thisWB.Sheets数字）。激活
 
 Cells.AutoFilter 
下一个
  

解决方案

测试：

  Dim ur As Range 
 Set ur = ThisWorkbook.Sheets （总数）。UsedRange 
 
 Application.ScreenUpdating = False 
 filterField = Application.Match（Host，ur.Rows（1），0）
如果不是IsError（filterField）然后
 
对于每个vall在countryCodes 
 rep =替换（thisWBName，EMEA，vall）
 
 ur.AutoFilter字段：= filterField， Criteria1：== *& vall& *
 
'仅复制数据的可见行
 ur.SpecialCells（xlCellTypeVisible）.Copy 
 
'仅粘贴可见行
工作簿（rep）.Worksheets（Overall Numbers）。Range（A1）。PasteSpecial xlPasteAll 
工作簿（rep）.Save 
 
 ur.AutoFilter 
下一个
结束如果
 Application.ScreenUpdating = True 
  

I have modified my Excel Macro, which before went row by row and it now filters the results and copies in bulk. Much more efficient.

The problem I encouter now, the Auto Filter adds Millions of empty rows to the Worksheet and I can't identify why it does so.

CountryCodes is a dictionary which contains the values for the filter. Criteria is looking for rows that contain the entry from the dictionary.

This is the code:

    For Each vall In CountryCodes
    thisWB.Activate
    thisWB.Sheets("Overall Numbers").Activate

    lookfor = CountryCodes.Item(vall)
    rep = Replace(thisWBName, "EMEA", lookfor)

    Set rng = ActiveSheet.Range("A1:Z1")

    FilterField = WorksheetFunction.Match("Host", rng.Rows(1), 0)

    If ActiveSheet.AutoFilterMode = False Then rng.AutoFilter

    rng.AutoFilter Field:=FilterField, Criteria1:="=*" & lookfor & "*", Operator:=xlFilterValues

    Set rng2 = ThisWorkbook.Worksheets("Overall Numbers").Cells.SpecialCells(xlCellTypeVisible)

    rng2.Copy Workbooks(rep).Worksheets("Overall Numbers").Range("A1")

    Workbooks(rep).Save

    thisWB.Activate
    thisWB.Sheets("Overall Numbers").Activate

    Cells.AutoFilter
Next

解决方案

Tested:

Dim ur As Range
Set ur = ThisWorkbook.Sheets("Overall Numbers").UsedRange

Application.ScreenUpdating = False
filterField = Application.Match("Host", ur.Rows(1), 0)
If Not IsError(filterField) Then

    For Each vall In countryCodes
       rep = Replace(thisWBName, "EMEA", vall)

       ur.AutoFilter Field:=filterField, Criteria1:="=*" & vall & "*"

       'copy visible rows with data only
       ur.SpecialCells(xlCellTypeVisible).Copy

       'paste visible rows with data only
       Workbooks(rep).Worksheets("Overall Numbers").Range("A1").PasteSpecial xlPasteAll
       Workbooks(rep).Save

       ur.AutoFilter
    Next
End If
Application.ScreenUpdating = True

这篇关于Excel VBA AutoFilter添加空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！