## Combine cell ranges eliminating blanks

*Article last updated on January 19, 2018*

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

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]

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

Array formulas allows you to do advanced calculations not possible with regular formulas.

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

The picture above shows how to merge two columns into one list using a formula. If you are looking […]

Combine data from multiple sheets

Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & […]

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 […]

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, […]

Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]

Create links to all sheets in a workbook

The macro demonstrated below creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]

Finding the shortest path – A * pathfinding

Two months ago I posted some interesting stuff I found: Shortest path. Let me explain, someone created a workbook that calculated […]

Split search value using delimiter and search for each substring

Anil asks: I have A1(anil singh raj) It can be anything Like A1(singh raj anil) I want return value in […]

Did you know that you can select all comments in the current sheet? Press F5, click "Special..." button, select "Comments" […]

### 4 Responses to “Combine cell ranges eliminating blanks”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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.