## Combine cell ranges ignore blank cells

The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also show you how to sort these values.

A user defined function is a custom function in Excel than anyone can build, you simply copy the code below to a module in the Visual Basic Editor and then enter the function name and arguments in a cell.

I have articles that shows you how to combine two and three columns using array formulas. Check out this article that demonstrates how to Merge tables based on a condition.

Array formula in cell range B3:B70:

#### How to create an array formula

- Select cell range B3:B70.
- Copy (Ctrl + c) above array formula.
- Paste (Ctrl + v) array formula to formula bar.

- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

#### VBA

'Name function and declare arguments Function MergeRanges(ParamArray arguments() As Variant) As Variant() 'Declare variables and data types Dim cell As Range, temp() As Variant, argument As Variant Dim iRows As Integer, i As Integer 'Redimension temp in order to let it grow if needed ReDim temp(0) 'Iterate through each cell range For Each argument In arguments 'Iterate through each cell in cell range For Each cell In argument 'If cell not equal to nothing If cell <> "" Then 'Save cell value to array variable temp(UBound(temp)) = cell 'Add another container to array ReDim Preserve temp(UBound(temp) + 1) End If Next cell Next argument 'Remove container from array ReDim Preserve temp(UBound(temp) - 1) 'Count cells occupied by user defined function iRows = Range(Application.Caller.Address).Rows.Count 'Add containers For i = UBound(temp) To iRows 'Add another container ReDim Preserve temp(UBound(temp) + 1) 'Save "" to array temp(UBound(temp)) = "" Next i 'Return array MergeRanges = Application.Transpose(temp) End Function

#### How to add the user defined function to your workbook

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

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

Recommended articles

Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

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

Function SelectionSort(TempArray As Variant) Dim MaxVal As Variant Dim MaxIndex As Integer Dim i As Integer, j As Integer ' Step through the elements in the array starting with the ' last element in the array. For i = UBound(TempArray) To 0 Step -1 ' Set MaxVal to the element in the array and save the ' index of this element as MaxIndex. MaxVal = TempArray(i) MaxIndex = i ' Loop through the remaining elements to see if any is ' larger than MaxVal. If it is then set this element ' to be the new MaxVal. For j = 0 To i If TempArray(j) > MaxVal Then MaxVal = TempArray(j) MaxIndex = j End If Next j ' If the index of the largest element is not i, then ' exchange this element with element i. If MaxIndex < i Then TempArray(MaxIndex) = TempArray(i) TempArray(i) = MaxVal End If Next i End Function

### Combine merge category

The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]

This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]

Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]

The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]

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

This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]

This article explains how to merge values row by row based on a condition in column A using an array […]

Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]

### For next statement category

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

Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left […]

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

The User Defined Function (UDF) demonstrated in this article, resizes a given range to columns or rows you specify. This […]

Table of Contents FOR NEXT statement FOR NEXT with a counter variable FOR each NEXT example FOR NEXT with counter […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 5 Responses to “Combine cell ranges ignore blank cells”

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

Paste image link to your comment.

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

Hi

I am finding an error on this line of code while compiling your vba of combining multiple cell ranges. Any inputs?

If cell <> "" Then