CODEWITHSUNDEEP
excelvba
bramvs
bramvs

Reputation: 39

how to paste part of VBA array to excel range

Is it somehow possible to past part of an vba array to an excel range?

I need to do something like this:

dim testday() as Variant
testday = Sheets("raw").Range("E745:BN745").Value
Sheets("raw").Range("E745:BN745").Value = ""
Sheets("Interface").Range("B4:E4") = testday(3, 4, 5, 6).Value

but this doesn't work... is there a way to fix this? Thx!

Upvotes: 1

Views: 18387

Answers (3)

Ioannis
Ioannis

Reputation: 5408

If the array you want to copy is one-dimensional, and you need to copy contiguous cells you can use the CopyMemory function:

Option Explicit
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)


Sub test()
  Dim vArr(), vArr2()
  Dim lCnt As Long
  Dim lStartIndex As Long, lFinishIndex As Long, lLength As Long


  With Application
    vArr = .Transpose(.Transpose(Range("A1:R1").Value2))
  End With

  lStartIndex = 3
  lFinishIndex = 6
  lLength = lFinishIndex - lStartIndex + 1

  ReDim vArr2(1 To lLength)

  CopyMemory vArr2(1), vArr(lStartIndex), lLength * 16

  For lCnt = LBound(vArr2) To UBound(vArr2)
    Debug.Print vArr2(lCnt)
  Next lCnt

  Range("A2").Resize(1, UBound(vArr2)).Value2 = vArr2

End Sub

Tested with first row being

67.2    9   57.2    boo 52  64  76  39  48  50  28  54  96  29  98  25  68  19

returns 

57.2    boo 52  64

on the second row. So your snippet would change as

dim testday(), testday2()
With Application  ' Value2 is faster than Value
  testday = .Transpose(.Transpose(Sheets("raw").Range("E745:BN745").Value2))
End With
Sheets("raw").Range("E745:BN745").ClearContents   ' Good suggestion by JFC
CopyMemory testday2(1), testday(3), 4 * 16        ' Variant = 16 Bytes
Sheets("Interface").Range("B4:E4").Value2 = testday2  ' I would do Resize instead

I hope this helps!

Upvotes: 1

Ravi Yenugu
Ravi Yenugu

Reputation: 3898

You can Slice the Array using Index function

Sub Slicer()
 Dim testday() As Variant
 testday = Sheets("raw").Range("E745:BN745").Value
 Sheets("raw").Range("E745:BN745").Value = ""
 Sheets("Interface").Range("B4:E4")= Application.Index(testday, 1, Array(3, 4, 5, 6))
End Sub

Upvotes: 2

Jean-François Corbett
Jean-François Corbett

Reputation: 38551

You can either:

  • Use a loop to copy the values you need to a new array, and write that to your range. If you're going to do this often, you can write a reusable function that does this. Or,

  • Read only what you need from your "raw" sheet, and write that to your range, as illustrated below. This is probably the simplest solution in your particular case. 


Dim testday() As Variant
testday = Sheets("raw").Range("G745:J745").Value ' only read what you need
Sheets("raw").Range("E745:BN745").ClearContents
Sheets("Interface").Range("B4:E4") = testday(3, 4, 5, 6).Value

Upvotes: 0

Related Questions