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

[Andreas Killer]

The Range object simply gives us only the first range from a multiple area and everything else does not... this is strange at first, but if you think about how you want the different areas in a 2D array, you come up with at least 2 solutions:

Vertically on top of each other or horizontally next to each other. The newer versions of Excel now also have formulas for this: VSTACK and HSTACK, in VBA we have to do this ourselves. If you're looking for the code of StackedArrays, please download the AddIn and look into the module modLowLevel.

Sub MultipleSourceCells_VerticalStack()
  Dim Source As Range, Dest As Range
  Dim Data
  
  'Create the scenario
  ScenarioA
  
  'Refer to some cells
  Set Source = Range("A4:C7,D2:E6,B9")
  'Read all values into a 2D-array
  Data = StackedArrays(Source)
  
  '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
 
Sub MultipleSourceCells_HorizontalStack()
  Dim Source As Range, Dest As Range
  Dim Data
  
  'Create the scenario
  ScenarioA
  
  'Refer to some cells
  Set Source = Range("A4:C7,D2:E6,B9")
  'Read all values into a 2D-array
  Data = StackedArrays(Source, True)
  
  '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

This brings us to the end of the first part, let's now look at how we deal with multiple areas in the finish.