CODEWITHSUNDEEP
excelvbafiltercopypaste
Passer-by
Passer-by

Reputation: 71

Copy data with filter applied using Excel VBA

I noticed that using Range.Copy and Range.SpecialCells(xlCellTypeVisible).Copy give the same result, and I don't quite understand why, what is actually the difference of the two?

As an simple example: If I have spreadsheet like the following (from A1:C6)

A B C
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5

If I filter out 3, then the visible data becomes:

A B C
1 1 1
2 2 2
4 4 4
5 5 5

When I was trying to copy the above data with filter applied to another range, I tested the following two methods had the same results, which only copied the data with "3" filtered out, but why? Method 1:

Range("A1:C6").Copy
Range("A10").PasteSpecial xlPasteValues

Method 2:

Range("A1:C6").SpecialCells(xlCellTypeVisible).Copy
Range("A10").PasteSpecial xlPasteValues

Intuitively, comparing these two, only the second should exclude the invisible filtered out data, but the first one seems should have included all the data, including the invisible ones. Now that they result says they are the same, and if they are, then what would the difference be?

Thank you all!

Upvotes: 0

Views: 151

Answers (2)

Alexei Sholomitskiy
Alexei Sholomitskiy

Reputation: 1

You expect Excel to execute commands from VBA sub and return result, different from you did job manually. But result will be the same - filtered cells only.

if you want to copy cells block with hidden cells, the simplest way - is to deactivate filter
if this way unacceptable, there is unefficient way: to get values each cell one by one, using cycle
Set rng=Range("A1:C6")
for each cell from rng do
str = cell.FormulaLocal
...
next cell

Upvotes: 0

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ
ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ

Reputation: 1682

Private Sub CommandButton1_Click()
   Dim v As Variant, r As Range, dest As Range
   Set r = Me.Range("A1:C6")  'filtered range to copy
   v = r
   Set dest = Me.Range("D10").Resize(r.Rows.CountLarge, r.Columns.CountLarge)
   dest = v
End Sub

the result of pressing the button

the result of pressing the button

Upvotes: 0

Related Questions