0

I want to copy values from one Excel column to another using VBA. To do it efficiently I use Range.Value. This works fine, except when the source range is filtered.

Sample VBA:

Sub Test()
    Range("D2:D4").Value = Range("B2:B4").Value
End Sub

Run on this simple sample Worksheet produces the expected results:

Sample worksheet, unfiltered

Now filter the Source column to exclude values B. Running the Test VBA produces the following:

enter image description here

Cell D4 should have value C, not A. (Extending the sample data and filter produces more bizarre results when doing a Range.Value assignment.)

WTF is going on, and what reasonable VBA will do a correct copy of values when source data are filtered?

feetwet
  • 3,248
  • 7
  • 46
  • 84
  • Copy/paste the visible cells `(SpecialCells xlVisible)`. Example of how to do that [here](https://stackoverflow.com/a/60660686/6706419) – urdearboy Feb 19 '21 at 19:00
  • @BigBen Can you clarify? The value assignment works fine for column ranges in every case I have tried that doesn't involve filtered rows. – feetwet Feb 19 '21 at 19:01
  • @feetwet thats the point. Value transfer only works in a continuous range. You have a non-continuous range i.e. rows are skipped – urdearboy Feb 19 '21 at 19:02
  • @urdearboy – So the object Range("A2:A4") is somehow broken if the range is in a filtered table? (But only *sort of* broken, because VBA will still do the operation without complaining.) – feetwet Feb 19 '21 at 19:05
  • Apparently. There's probably a dupe here somewhere. [This seems related](https://stackoverflow.com/questions/10159073/copying-an-array-to-a-filtered-range-gives-irrational-results). – BigBen Feb 19 '21 at 19:06
  • This might suggest that `D2:D4` is broken, not `A2:A4`. – BigBen Feb 19 '21 at 19:21
  • [Also related here](https://stackoverflow.com/q/65969051/2662901). Slightly different examples, and nobody seems to know exactly what Excel is doing with filtered rows or why. – feetwet Feb 19 '21 at 19:21
  • It looks that you completely ignore the key of the problem. It has been offered in the first comment: `(SpecialCells xlVisible)`... When try copying the range `Value` this acts like an array and works as expected only in continuous range. In case of discontinuous range it returns only **the first area**. Until the first space... – FaneDuru Feb 19 '21 at 19:59
  • The only way I know this would work is if you would remove the filter. Yesterday I answered *copy active row and insert below even with active filter* [here](https://stackoverflow.com/questions/66275031/copy-active-row-and-insert-below-even-with-active-filter/66277566#66277566). The second procedure is backing up the filters to an array, while the third is reapplying the backed up filters. The first is showing how to use it. – VBasic2008 Feb 20 '21 at 00:37

0 Answers0