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 […]
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]
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 […]
What's on this page Finding the shortest path - A * pathfinding Optimize pick path in a warehouse 1. […]
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.
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...
Function MergeRanges(ParamArray Arguments() As Variant) As Variant() Dim X As Long, Index As Long, Cell As Variant, TempArray As Variant, Done As Boolean ReDim TempArray(1 To Application.Caller.Count) Index = 1 For X = LBound(Arguments) To UBound(Arguments) For Each Cell In Arguments(X) If Len(Cell.Value) Then TempArray(Index) = Cell.Value Index = Index + 1 If Index > UBound(TempArray) - LBound(TempArray) + 1 Then Done = True Exit For End If End If Next If Done Then Exit For Next For X = Index To UBound(TempArray) TempArray(X) = "" Next MergeRanges = Application.Transpose(TempArray) End FunctionRick 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