## Combine cell ranges eliminating blanks

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

Learn the basics of Excel arrays

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)

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 […]### 4 Responses to “Combine cell ranges eliminating blanks”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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