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!

There are numerous ways to accomplish the task of combining data. You could use arrays or a custom function which does the 'calculation on the data' and pulls only the result into the 'summary tool'? Or, step outside of VBA and use power query, or a database. This question seems too broad. — Michael Murphy

Gary's Student Gary's Student · Accepted Answer · 2020-04-07T13:26:25

Fortunately there is a built-in feature for Autofilter to facilitate this. Say we start with:

and apply a filter to it with:

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

==> The Autofilter has a Range property that allows: <==

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

The result on Sheet2 has

only the visible data from Sheet1
the header row
is not filtered

NOTE:

Looping is not required.
Similar approach can be used for Tables.
SpecialCells is not required.

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

1 Answers