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