从筛选表的一列复制/粘贴/计算可见单元格 [英] 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列中的国家，城市和语言。以下代码自动过滤范围，然后仅将自动过滤数据的列之一粘贴到另一个工作表。您应该可以修改此目的用于您的目的：

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屋！