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!
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
Array(3,4,5) I would use Evaluate("TRANSPOSE(ROW(" & lStartIndex & ":" & lFinishIndex & "))"), which makes a one-dimensional array of numbers starting with lStartIndex all the way to lFinishIndex, and can be used to allocate an arbitrary long (contiguous) array dynamically.
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!
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
Sheets("raw").Range("E745:BN745").ClearContentsClearContentsdoesn't remove the conditional formatting.