Merge two ranges together and wrap them into each other

H

HJA14

Board Regular
Joined
Apr 12, 2016
Messages
60
Hi all,

I want to merge two ranges together. The first range is equal to

Code: 
Sheets("1").Range("B1:AA100")

the second range is equal to

Code: 
Sheets("2").Range("B1:AA100")

I am familiar with the UNION-function, but I want to merge them differently.
Basically, I want to merge them column for column. Visually it looks as follows:

https://ibb.co/F4d4sdw


I tried using the following for each range

Code: 
Sub inser_columns()
For c = 2 to lastColumn Step 2
   Columns(c).Insert Shift:=xlToRight
Next
End Sub
And paste the two new ranges together by skipping blanks. However, this is very slow especially when I increase the sizes of the two ranges.
Help is much appreciated

F4d4sdw
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Try this

Code: 
Sub MergeRanges()
    Dim sh1 As Range, sh2 As Range, cel As Range, c As Long
    Set sh1 = Sheets("1").Range("B1:AA100")
    Set sh2 = Sheets("2").Range("B1:AA100")
    Set cel = Sheets("Merge").Cells(1, 2)

    For c = 0 To sh1.Columns.Count - 1
        sh1.Resize(, 1).Offset(, c).Copy cel.Offset(, c * 2)
        sh2.Resize(, 1).Offset(, c).Copy cel.Offset(, c * 2 + 1)
    Next c
End Sub



Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#FFFF00]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#FFFF00]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#FFFF00]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#FFFF00]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#FFFF00]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#FFFF00]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#FFFF00]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#FFFF00]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#FFFF00]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#FFFF00]G1[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: 1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#C00000]G1[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: 2[/td][/tr][/table]

RESULT

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][th]
L​
[/th][th]
M​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#FFFF00]G1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#FFFF00]G1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#FFFF00]G1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#FFFF00]G1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#FFFF00]G1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#FFFF00]G1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#FFFF00]G1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#FFFF00]G1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#FFFF00]G1[/td][td=bgcolor:#C00000]G1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td=bgcolor:#FFFF00]B1[/td][td=bgcolor:#C00000]B1[/td][td=bgcolor:#FFFF00]C1[/td][td=bgcolor:#C00000]C1[/td][td=bgcolor:#FFFF00]D1[/td][td=bgcolor:#C00000]D1[/td][td=bgcolor:#FFFF00]E1[/td][td=bgcolor:#C00000]E1[/td][td=bgcolor:#FFFF00]F1[/td][td=bgcolor:#C00000]F1[/td][td=bgcolor:#FFFF00]G1[/td][td=bgcolor:#C00000]G1[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Merge[/td][/tr][/table]
 
Upvote 0
Since I had been playing around with this too, I will post my offering. This is a bit different as it does not require any looping. My testing showed it to be about 3 times as fast as the post 2 code though I do note that the range size is not very big so that speed difference is basically irrelevant. Anyway, if you feel like it you can give this a whirl as well.
My code assumes ..
- Sheets 'Merge' exists but does not contain data
- In Sheets '1' and '2', the column immediately to the right of the range in question (range AB1:AB100 with the current specs) does not contain data. A modification could be made if that is not the case.

Rich (BB code): 
Sub Merge_Ranges()
  Dim a As Variant, vCols As Variant, vRws As Variant
  Dim cel As Range
  
  Const sRngAddress  As String = "B1:AA100" '<- Edit this if you want to adapt the code to a different range

  With Range(sRngAddress)
    vCols = Split(Join(Application.Transpose(Evaluate("row(" & .Column & ":" & .Columns.Count + .Column - 1 & ")")), "," & .Columns.Count + .Column & ","), ",")
    vRws = Evaluate("row(" & .Row & ":" & .Rows.Count + .Row - 1 & ")")
  End With
  Set cel = Sheets("Merge").Range(sRngAddress).Cells(1)
  a = Application.Index(Sheets("1").Cells, vRws, vCols)
  cel.Resize(UBound(a), UBound(a, 2)).Value = a
  a = Application.Index(Sheets("2").Cells, vRws, vCols)
  With cel.Offset(UBound(a)).Resize(UBound(a), UBound(a, 2))
    .Value = a
    .Copy
    cel.Offset(, 1).PasteSpecial xlPasteValues, SkipBlanks:=True
    .ClearContents
 End With
End Sub
 
Upvote 0
Hi,
You might try this as well, if you like
Code: 
Sub Merge()
    Dim sh1 As Range, sh2 As Range, cel As Range, c As Long
    a = Application.Transpose(Sheet1.Range("B1").CurrentRegion)
    b = Application.Transpose(Sheet2.Range("B1").CurrentRegion)
    Set dest = Sheet3
    k = 1
    cl = UBound(a, 2)
    dest.Activate
    For i = 1 To UBound(a, 1) * 2 - 1 Step 2
        Range(Cells(1, i), Cells(cl, i)).Offset(, 1) = Application.Transpose(Application.Index(a, k, 0))
        Range(Cells(1, i + 1), Cells(cl, i + 1)).Offset(, 1) = Application.Transpose(Application.Index(b, k, 0))
        k = k + 1
    Next i
End Sub

Flexible, Fast, ....
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA to merge duplicate rows and sum values in certain column with adjustments
Replies
9
Views
868
Alex Blakenburg
A
I
  • Solved
Text to columns Fixed With merging two columns with a decimal
Replies
2
Views
337
ItalianPlatinum
I
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
402
Akuini
Akuini
C
  • Solved
Merge based on Merged area of adjacent columns
Replies
2
Views
1K
CodeNewb
C
M
  • Solved
Deleting rows from a Range variable
Replies
4
Views
247
Umma123
U

Forum statistics

Threads
1,223,630
Messages
6,173,453
Members
452,514
Latest member
cjkelly15

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top