Append Named Ranges one under the other to form a new Range

TamirBasin

New Member
Joined
Apr 11, 2017
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have many ranges on the same sheet, each contains exactly 1 column.
Range1, Range2, Range3...etc.

I need to create a combined range ("AppendedRange") that will append all the ranges, one under another (for vlookup purposes)
Range1 (values)
Range2 (values)
Range3 (values)
etc.

I got lost with trying to loop and update the last row of the AppendedRange.

I would appreciate any help.
Thanks,
Tamir
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Tamir

Can you post your current code?
 
Upvote 0
An alternative is to use a UDF (User Defined Function)

Suppose you have something like this


A
B
C
D
E
F
G
H
I
J
1
Range1​
Search​
ColToGet​
Result​
2
a​
10​
Range2​
f​
2​
60​
3
b​
20​
d​
40​
a​
2​
10​
4
c​
30​
e​
50​
k​
2​
110​
5
f​
60​
6
g​
70​
7
8
Range3​
9
h​
80​
10
i​
90​
11
j​
100​
12
k​
110​
13
l​
120​
14
m​
130​
15

<tbody>
</tbody>


Where
Range1 --> gray area
Range2 --> blue area
Range1 --> yellow area

Alt+F11 to open the VBEditor
Menu
Insert > Module

Copy and paste the code below in the right panel
Code:
Function VlookupMULTI(myVal As String, myCol As Long, ParamArray myRanges() As Variant)
    Dim r As Variant, rFound As Range, bFound As Boolean
        
    For Each r In myRanges
        If TypeOf r Is Range Then
            Set rFound = r.Columns(1).Find(What:=myVal, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
            If Not rFound Is Nothing Then
                bFound = True
                Exit For
            End If
        End If
    Next r
    
    If bFound Then
        VlookupMULTI = rFound.Offset(, myCol - 1)
    Else
        VlookupMULTI = "Not Found"
    End If
    
End Function

Criteria in columns H:I

Formula in J2 copied down
=VlookupMULTI(H2,I2,Range1,Range2,Range3)

Hope this helps

M.
 
Upvote 0
Hi,
There is the code...
Code:
Sub CopyRange()
Dim NamedRange As Name, nm As Name, AppendedRange As Range
Dim wb As Workbook




Set AppendedRange = Range("AppendedRanges")


i = 1
Set wb = ActiveWorkbook
Debug.Print ActiveWorkbook.Names.Count
AppendedRange_FirstRow = Range("AppendedRanges").Row


For Each nm In ActiveWorkbook.Names


    Set NamedRange = wb.Names.Item(i)
    
    If NamedRange = "AppendedRanges" Then GoTo NextIteration
    End If
    
    NamedRange.Copy Destination:=Cells(AppendedRange_LastRow, AppendedRange_Column)
    AppendedRange_Column = Range("AppendedRanges").Column
    AppendedRange_LastRow = Cells(Rows.Count, AppendedRange_Column).End(xlUp).Row
    AppendedRange.RefersTo = AppendedRange.RefersToRange.Resize(AppendedRange_LastRow + 1, 1)
    
    i = i + 1
 
    'clear memory
     Set ws = Nothing
     Set NamedRange = Nothing
     
Next nm


End Sub
 
Upvote 0
Thanks Marcelo,
I do need to append the ranges as it supports an already exist set of forms and many other calculations done in the company.
Since the ranges are changing all the time, I already made a macro that dynamically updates them.
So, I need this appended range and it will be updated all the time as well.
Tamir
 
Upvote 0
Do all the named ranges have the same no of columns?

Where do you want the result to end up?

Why don't you copy all the named ranges one below the other and then set/resize the named range to refer to them all?
 
Upvote 0
Do all the named ranges have the same no of columns?
All ranges have 1 column only.

Where do you want the result to end up?
I want the result to be on the same sheet starting at A2.

Why don't you copy all the named ranges one below the other and then set/resize the named range to refer to them all?
That is exactly what I want to do but with a macro. The various ranges are already set on the sheet and there number of rows is changing daily. I have a macro that updates the ranges of all Named Ranges.
 
Upvote 0
This worked for me, consolidating all the ranges on Sheet1 into a range starting at A2 and calling the resultant range AppendedRanges.
Code:
Sub ConsolidateRngs()
Dim nm As Name
Dim rngDst As Range
Dim rngSrc As Range

    Set rngDst = Sheets("Sheet1").Range("A2")

    If [ISREF(AppendedRanges)] Then
        Range("AppendedRanges").ClearContents
    End If

    For Each nm In ActiveWorkbook.Names
        If nm.Name <> "AppendedRanges" Then
            If nm.RefersToRange.Parent.Name = "Sheet1" Then
                nm.RefersToRange.Copy
                rngDst.PasteSpecial xlPasteValues
                Set rngDst = rngDst.Offset(nm.RefersToRange.Rows.Count)
            End If
        End If
    Next nm

    With Sheets("Sheet1")
        .Range("A2", rngDst.Offset(-1)).Name = "AppendedRanges"
    End With

    Application.CutCopyMode = False
    
End Sub
 
Last edited:
Upvote 0
This is an example of what I am trying to do

s!AqDxOQKeFIy4blQmevJvtAvTgSk

https://1drv.ms/i/s!AqDxOQKeFIy4blQmevJvtAvTgSk
 
Upvote 0
The code I posted should do that though you might need to change the destination range - in post #7 you mentioned that started at A2 so that's what I used in the code I posted.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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