Store a range in a Variant Variable

[Rudi]

Hi,

Can I store a whole range of data in a Variant and then write it to another sheet?
The code below currently writes only the first cells value into the destination sheet?
TX

Code: Select all

Sub ProcessCapital()
   Dim varResult As Variant, i As Integer
   Dim sBranch As String, sCat As String, sAccNo As String, sActWB As String
   Dim vItem1 As Variant, vItem2 As Variant, vItem3 As Variant
   
   varResult = Application.GetOpenFilename _
               (FileFilter:="Excel Files, *.xls*", MultiSelect:=True)
   
   For i = LBound(varResult) To UBound(varResult)
      
      Workbooks.Open Filename:=varResult(i), UpdateLinks:=False
      sActWB = ActiveWorkbook.Name
      Sheets("Capital").Select
      
      sBranch = Range("A2").Value
      sCat = Mid(Range("A10").Value, 9, Len(Range("A10").Value))
      sAccNo = Mid(Range("A10").Value, 1, 5)
      vItem1 = Range("A11:E29").Value
      vItem2 = Range("A31:E55").Value
      vItem3 = Range("A57:E67").Value
      
      Workbooks(sActWB).Close False
      
      Range("E1000000").End(xlUp).Offset(1, 0).Value = vItem1
      Range("E1000000").End(xlUp).Offset(1, 0).Value = vItem2
      Range("E1000000").End(xlUp).Offset(1, 0).Value = vItem3
       
   Next i
End Sub

[Rudi]

OK, I seem to have resolved it with this code (below)
It's obviously to do with the size factor.

It would have been great if (like copy and paste) one could store the range variable and specify 1 destination cell (like: A1) and the variable would expand the range to dump everything...

Code: Select all

Sub ProcessCapital2()
   Dim varResult As Variant, i As Integer
   Dim sBranch As String, sCat As String, sAccNo As String, sActWB As String
   Dim rItem1 As Range, rItem2 As Range, rItem3 As Range
   
   varResult = Application.GetOpenFilename _
               (FileFilter:="Excel Files, *.xls*", MultiSelect:=True)
   
   For i = LBound(varResult) To UBound(varResult)
      
      Workbooks.Open Filename:=varResult(i), UpdateLinks:=False
      sActWB = ActiveWorkbook.Name
      Sheets("Capital").Select
      
      sBranch = Range("A2").Value
      sCat = Mid(Range("A10").Value, 9, Len(Range("A10").Value))
      sAccNo = Mid(Range("A10").Value, 1, 5)
      
      Set rItem1 = Workbooks(sActWB).Sheets("Capital").Range("A11:E29")
      Set rItem2 = Workbooks(sActWB).Sheets("Capital").Range("A31:E55")
      Set rItem3 = Workbooks(sActWB).Sheets("Capital").Range("A57:E67")
      
      ThisWorkbook.Activate
      
      Range("E1000000").End(xlUp).Offset(1, 0).Resize(rItem1.Rows.Count, 5).Value = rItem1.Value
      Range("E1000000").End(xlUp).Offset(1, 0).Resize(rItem2.Rows.Count, 5).Value = rItem2.Value
      Range("E1000000").End(xlUp).Offset(1, 0).Resize(rItem3.Rows.Count, 5).Value = rItem3.Value
      
      Workbooks(sActWB).Close False
       
   Next i
End Sub

[HansV]

Copy and Paste/PasteSpecial have a lot of extra "intelligence" built in. This is not available when you work with an array...

[Rudi]

In your professional opinion, which is better (more optimal):
Copy/PasteSpecial
or
Store Array and Dump?

[HansV]

If you simply want to copy and paste or paste special, the built-in methods are more efficient. I performed a test with a range of 2000 rows by 15 columns; I copied it 1000 times to another range.
Copy/PasteSpecial took 12 seconds, while using an array took 30 seconds.

If you want to manipulate the values in between, it might be different - you'd have to experiment.

[Rudi]

TX. Appreciate that test.