## Excel udf: Combine cell ranges into a single range while eliminating blanks

This is an answer to Shawna´s question. The following user defined function merges up to 255 cell ranges and removes blanks. Later in this post I will also demonstrate how to sort these values.

**Userdefined function in cell range B3:B70, entered as an array formula:**

=MergeRanges(D2:D30, F2:F30, Sheet2!C1:C35)

Sort text from multiple cell ranges combined (user defined function):

**Sort text cells alphabetically from two columns using excel array formula**

Comments(13) Filed in category: Excel, Sort values

**How to create an array formula**

- Select cell range B3:B70.
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

**Learn the basics of Excel arrays**

Comments(2) Filed in category: Count values, Excel

**VBA:**

Function MergeRanges(ParamArray arguments() As Variant) As Variant() Dim cell As Range, temp() As Variant, argument As Variant Dim iRows As Integer, i As Integer ReDim temp(0) For Each argument In arguments For Each cell In argument If cell <> "" Then temp(UBound(temp)) = cell ReDim Preserve temp(UBound(temp) + 1) End If Next cell Next argument ReDim Preserve temp(UBound(temp) - 1) iRows = Range(Application.Caller.Address).Rows.Count For i = UBound(temp) To iRows ReDim Preserve temp(UBound(temp) + 1) temp(UBound(temp)) = "" Next i MergeRanges = Application.Transpose(temp) End Function

**How to use user defined function in excel**

- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste vba code
- Exit visual basic editor

**Download excel example file**

Combine ranges.xls

(Excel 97-2003 Workbook *.xls)

## Merge,sort and remove blanks from multiple cell ranges

I used the "Sort array" function found here: Using a Visual Basic Macro to Sort Arrays in Microsoft Excel (microsoft) with some small modifications.

**Download excel example file**

Merge and sort cell values.xlsm

(Excel 97-2003 Workbook *.xls)

### Category: Combinemerge

Merge two columns into one list in excel

Question: How do I merge two ranges into one list? Answer: Formula in C2: =IFERROR(INDEX($A$2:$A$6, ROWS(C1:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C1:$C$1)-ROWS($A$2:$A$6)), "")) Copy […]Comments(50) Filed in category: Combine/Merge, Excel

Combine data from multiple sheets in excel

Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & […]Comments(29) Filed in category: Combine/Merge, Excel

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]Comments(28) Filed in category: Combine/Merge, Excel

Merge three columns into one list in excel

Question: How do I merge three columns into one list? Answer: Excel 2007 array formula in D2: =IFERROR(INDEX(List1, ROWS(D1:$D$1)), IFERROR(INDEX(List2, […]Comments(26) Filed in category: Combine/Merge, Excel

Consolidate sheets in excel (vba)

Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]Comments(11) Filed in category: Combine/Merge, Excel

Comments(7) Filed in category: Combine/Merge, Excel

Group a number of rows together by the first column

Mike asks: Oscar, I'm hoping you can help. I am trying to group a number of rows together by the […]Comments(5) Filed in category: Combine/Merge, Excel

Automate excel: Update list with new values

Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]Comments(4) Filed in category: Combine/Merge, Excel

Tracking a stock portfolio #2 in excel

This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]Comments(3) Filed in category: Combine/Merge, Excel, Finance

### 4 Responses to “Excel udf: Combine cell ranges into a single range while eliminating blanks”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Redim Preserve does not execute all that quickly, so it is usually a good idea to avoid using it too often. Here is an alternate function to the one you posted which avoids them altogether...

Rick Rothstein (MVP - Excel),

I didn´t know! I am curious, I have to do some speed tests.

Thank you for your valuable contribution!

This works well, but doesn't work if you have strings over 255 letters long! Any idea how to work around that? Thanks, Tom

I've combined several cell ranges across several sheets, how would I eliminate the duplicate cells using the vba provided?

Trying to generate a list based on several cell ranges on Sector A-P sheets and combine totals on a Totals sheet.