## 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)

**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.

**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

Question: How do I merge two ranges into one list? Answer: Excel 2007 array formula in C2: =IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, […]

Comments(50) Filed in category: Combine/Merge, 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

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

### Category: User defined functions udf

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Comments(17) Filed in category: Excel, Sort values, Unique distinct values, User defined functions (udf), VBA

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Comments(16) Filed in category: Count values, Excel, Frequency, User defined functions (udf), VBA

### Category: Vba

This post describes how to copy values between sheets. I am using the invoice template sheet. This macro copies rows […]

Comments(40) Filed in category: Excel, VBA

This post describes how to copy selected rows using checkboxes. In a previous post I showed you how to add/remove […]

Comments(30) Filed in category: Check-boxes, Excel, VBA

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

### 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.