excel - VBA Copy visible cells and paste as values - most efficient method

Question

I have an excel tool that uses feed from 11 different raw data files generated from reporting system to do some calculations on that data.

The process is very simple: - open file - filter contents - copy filtered contents - paste into another file (the summary tool's tab)

As the amounts of data and individual files rose I started getting more issues with memory.Hence my question - what is the most memory and speed efficient way to copy/paste these tables?

• (...).SpecialCells(xlCellTypeVisible).Copy Destination:=(...) - I haven't found the way to include XLValues parameter for this (I want to minimise the resources taken up by copying formatting, there are no formulas)

• using defined ranges for the copy/paste purpose (name range with xlCellTypeVisible parameter, converting to another range to get the values only and sending the range to destination) - that would require additional variables for the ranges

• plain old Columns(...).SpecialCells(xlCellTypeVisible).Copy and Range.("A1").PasteSpecial Paste:=xlValues - this method has both "only visible cells" and "paste values only" bits that I'm looking for, but it uses the clipboard as a middleman and I guess this is eating up my memory

Maybe there is yet another method that I don't know of?

Appreciate any insights!

标签： excelvbacopy-paste

Answer 1

幸运的是，Autofilter 有一个内置功能可以促进这一点。假设我们从：

并使用以下方法对其应用过滤器：

Sub Macro1()
    Columns("A:B").AutoFilter
    ActiveSheet.Range("$A$1:$B$8").AutoFilter Field:=2, Criteria1:=">50", Operator:=xlAnd
End Sub

==>自动过滤器有一个Range属性允许：<==

Sub Kopy()
    Dim rng As Range
    Set rng = ActiveSheet.AutoFilter.Range
    rng.Copy Sheets("Sheet2").Range("A1")
End Sub

结果上Sheet2有

1. 只有来自的可见数据Sheet1
2. 标题行
3. 未过滤

笔记：

不需要循环。
类似的方法可以用于Tables。
不需要 SpecialCells。