Author: Oscar Cronquist 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

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

How to enter an array formula

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


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

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste vba code
  4. 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

Merge, sort and remove blanks - user defined function

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)