[Article & AddIn] How to paste into a filtered range

[Andreas Killer]

If you are only interested in the universal array routines, take a look at the modLowLevel module, which contains everything you need and read only the last part of this topic.

However, it will be worthwhile for you to follow my explanations, because the basics of arrays are also worth knowing. Here is the example file:
https://www.dropbox.com/scl/fi/q80in...2d1m55ecb&dl=1

Over the years I have seen a lot of code and there are many ways to write something in cells. At the beginning, you usually start with Cells(y,x) = Value and specify the absolute positions. Then you learn that there is a Range object, you can put it somewhere and the top left place is always y1,x1. Few programmers then come to Offset and Resize to adjust the areas and only much later, when you process large amounts of data, you notice that the code is slow and many complain in a forum. Here you learn that it is up to 700% faster to write data to a sheet with a two-dimensional array.

What luck for us that our previous routines all provide such an array.

If you look at the main routines PasteHTMLIntoAutofilter and PasteXMLIntoAutofilter in modMain, they probably look strange to you. Therefore, I would like to give you a little more understanding of this methodology, because it doesn't get any faster than that; don't be fooled by the amount of code, a long code is not necessarily slower than a short one!

Let's make a new file and start a few simple routines to see what happens in reality. So that you don't have to copy each routine individually, I provide you with the file here.
If you want to see the results of my remarks, just run the corresponding macro. You can also do this multiple times; each macro creates the scenario from scratch.

First, let's create some data.

Sub ScenarioA()
  Dim Dest As Range
  'Clear all cells
  Cells.Clear
  'Refer to some cells
  Set Dest = Range("A1:E10")
  With Dest
    'Write a formula into to get the address of the cell
    .Formula = "=ADDRESS(ROW(),COLUMN(),4)"
    'Convert the formulas to values (using a 2D-array "on the fly")
    .Value = .Value
  End With
End Sub

Now let's read some of the data and write it to another place... you may have done it before. But what actually happens when the target area is smaller?

Sub SmallerDestination()
  Dim Source As Range, Dest As Range
  Dim Data
  
  'Create the scenario
  ScenarioA
  
  'Refer to some cells
  Set Source = Range("A4:C7")
  'Read all values into a 2D-array
  Data = Source.Value
  
  'Refer to the destination cells
  Set Dest = Range("G3:H5")
  'Write the array into the sheet
  Dest.Value = Data
  
  'Just to show you what was copied
  Source.Select
End Sub

As we can see, data is missing, the Range object writes only as much of the array as possible, there is no error message. And what about in the other case, if the target area is larger?

Sub LargerDestination()
  Dim Source As Range, Dest As Range
  Dim Data
  
  'Create the scenario
  ScenarioA
  
  'Refer to some cells
  Set Source = Range("A4:C7")
  'Read all values into a 2D-array
  Data = Source.Value
  
  'Refer to the destination cells
  Set Dest = Range("G3:J8")
  'Write the array into the sheet
  Dest.Value = Data
  
  'Just to show you what was copied
  Source.Select
End Sub

Surprisingly, Excel fills the additional cells with an error and does not leave them empty, or even keeps any existing content... That's why you see code like this everywhere, the areas have to be exactly the same size.

Sub ExactDestination()
  Dim Source As Range, Dest As Range
  Dim Data
  
  'Create the scenario
  ScenarioA
  
  'Refer to some cells
  Set Source = Range("A4:C7")
  'Read all values into a 2D-array
  Data = Source.Value
  
  'Refer to the top left destination cell
  Set Dest = Range("G3")
  'Resize the refered range to the same size as the array
  Set Dest = Dest.Resize(UBound(Data), UBound(Data, 2))
  'Write the array into the sheet
  Dest.Value = Data
  
  'Just to show you what was copied
  Source.Select
End Sub