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

  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.

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

  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)