VBA copy two separate cells

[Bill Wisdom]

I am trying to get VBA to copy two separate cells, this is just an example: A1, & D1. Data is in A1:D1. Then paste values to two consecutive cells (ie A5:B5).. It may be anywhere on the worksheet. I've tried Range(Selection, Selection.Offset(0,3) ). That just copies range ("A1:D1"). Any help is greatly appreciated.

 

[Chihiro]

Can't think of way to do it in one shot off top of my head.

Just do it one cell at a time. Or if there's more than just the two cells you need to deal with...

First put entire range into variant array and have another array object in stand-by. Loop and count elements that meet condition.

ReDim stand-by array with dimensions obtained from first pass on the loop.
Then loop on the variant array again and fill stand-by array with values from it.

Then put back values into desired location.

Ex: Done for only non-contiguous columns in a row.

Sub Demo()
Dim myAr, resAr()
Dim colCnt As Long, i As Long
myAr = Range("A1:D1").Value

For i = 1 To UBound(myAr, 2)
    If Len(myAr(1, i)) > 0 Then
        colCnt = colCnt + 1
    End If
Next

ReDim resAr(1, colCnt)
colCnt = 0
For i = 1 To UBound(myAr, 2)
    If Len(myAr(1, i)) > 0 Then
        resAr(0, colCnt) = myAr(1, i)
        colCnt = colCnt + 1
    End If
Next

Range("A5").Resize(, UBound(resAr, 2)) = resAr


End Sub

As you can see, it's bit of overkill for your description.

 

[Chihiro]

Oh wait... UNION can be used here.

Sub Demo()
Union(Range("A1"), Range("D1")).Copy Range("A5")
End Sub

However, do note that Union has bit of constraint.

It will honour the order you Union Range in. But with exception of contiguous range.

Ex:

Union(Range("D1"), Range("A1"), Range("F1"))

Will have D1, A1, F1 value in that order. Until you paste it into range.
Then order becomes left to right.

However...

Union(Range("D1"),Range("B1"),Range("A1"))

Will rearrange order even before it's pasted back into range.

This is due to how Excel/Union treats contiguous range.
Contiguous range will always be combined and will order it left to right (or right to left depending on your Excel setting).

If you want to keep order in first case, you need to put Union value into array. In second case, instead of union array must be used from get go.

Edited for clarity and grammar.

 

[Bill Wisdom]

You’re right. Overkill. Think I’ll just put the data in manually.

 

[Bill Wisdom]

But thank you for trying. I thought that if was just something I was doing wrong and it was an easy fix , I’d implement it. But it looks like it is going to be difficult.. You can stop working on this project.. thank you for your time.