从过滤表的一列复制/粘贴/计算可见单元格 [英] Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table

问题描述

我正在使用 AutoFilter 对 VBA 中的表格进行排序，从而生成较小的数据表格.我只想在应用过滤器后复制/粘贴一列的可见单元格.另外，我想平均一列的过滤值并将结果放在不同的单元格中.

I am using AutoFilter to sort through a table in VBA, which results in a smaller table of data. I only want to copy/paste the visible cells of one column after the filter is applied. Also, I would like to average the filtered values of one column and put the result in a different cell.

我在 Stack 上找到了这个片段，它允许我复制/粘贴过滤器的整个可见结果，但我不知道如何修改它或以其他方式只获取一列的数据(没有头)从它.

I've found this snippet on Stack which allows me to copy/paste the entire visible results of the filter, but I don't know how to modify it or another way to get only one column's worth of data (without the header) from it.

Range("A1",Cells(65536,Cells(1,256).End(xlToLeft).Column).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

答案的补充(用过滤后的值计算):

tgt.Range("B2").Value =WorksheetFunction.Average(copyRange.SpecialCells(xlCellTypeVisible))

推荐答案

我在 Sheet1 上设置了一个简单的 3 列范围，在 A、B 和 C 列中使用 Country、City 和 Language.以下代码自动过滤该范围然后仅将其中一列自动过滤的数据粘贴到另一张工作表中.您应该能够根据自己的目的进行修改:

I set up a simple 3-column range on Sheet1 with Country, City, and Language in columns A, B, and C. The following code autofilters the range and then pastes only one of the columns of autofiltered data to another sheet. You should be able to modify this for your purposes:

Sub CopyPartOfFilteredRange()
    Dim src As Worksheet
    Dim tgt As Worksheet
    Dim filterRange As Range
    Dim copyRange As Range
    Dim lastRow As Long

    Set src = ThisWorkbook.Sheets("Sheet1")
    Set tgt = ThisWorkbook.Sheets("Sheet2")

    ' turn off any autofilters that are already set
    src.AutoFilterMode = False

    ' find the last row with data in column A
    lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row

    ' the range that we are auto-filtering (all columns)
    Set filterRange = src.Range("A1:C" & lastRow)

    ' the range we want to copy (only columns we want to copy)
    ' in this case we are copying country from column A
    ' we set the range to start in row 2 to prevent copying the header
    Set copyRange = src.Range("A2:A" & lastRow)

    ' filter range based on column B
    filterRange.AutoFilter field:=2, Criteria1:="Rio de Janeiro"

    ' copy the visible cells to our target range
    ' note that you can easily find the last populated row on this sheet
    ' if you don't want to over-write your previous results
    copyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("A1")

End Sub

请注意，通过使用上述语法进行复制和粘贴，不会选择或激活任何内容(在 Excel VBA 中应始终避免这种情况)并且不会使用剪贴板.因此，Application.CutCopyMode = False 不是必需的.

Note that by using the syntax above to copy and paste, nothing is selected or activated (which you should always avoid in Excel VBA) and the clipboard is not used. As a result, Application.CutCopyMode = False is not necessary.

这篇关于从过滤表的一列复制/粘贴/计算可见单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！