Back in Episode 578, I used an incredibly complex method using NotePad for joining cells together. Today, Jonathan from the UK points out that a few lines of VBA code would have made the solution incredibly simple. Episode 587 shows you how.
Function ConcatenateRange(rCells As Range)
Dim vTemp As Variant
Application.Volatile
For Each vTemp In rCells
ConcatenateRange = ConcatenateRange & vTemp & " "
Next vTemp
End Function
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Function ConcatenateRange(rCells As Range)
Dim vTemp As Variant
Application.Volatile
For Each vTemp In rCells
ConcatenateRange = ConcatenateRange & vTemp & " "
Next vTemp
End Function
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, we're gonna revisit, episode 578.
This is where we were trying to put a whole bunch of words together into a single cell, without having to build a concatenation formula.
And Jonathan from the UK wrote it and said, "Hey! I know you don't like to cover VBA in the podcast, but this quick user-defined function would really be that with the way to go." So, I'm gonna take advantage of Jonathan's email here and just point out that my book, 'VBA and Macros for Microsoft Excel', is now out for Excel 2007.
This is a basically the same book that we had for Excel 2003.
Along with the new features that are new in 2007.
So, if you've upgraded to 2007 or are thinking about upgrading, certainly go ahead and get that.
Sorting has changed, the new pivot tables, data visualizations.
We covered those items in Excel 2007 and again the book is designed for someone who knows Excel very very well.
But, wants to get up the VBA learning curve.
Maybe, you recorded a few macros.
Maybe they worked, they probably didn't work.
The book will tell you how to get around that.
So, Jonathan said, you know, rather than trying to go out to notepad.
Which is what I actually did here in episode 578.
It'd be much easier just to have a custom function to put these words together.
So, what we'll do is we'll hit [ alt+ F11 ].
That'll take us to the VBA editor.
We're gonna use insert module, to add a little bit of a little module.
Jonathan sent me the code.
It's for concatenate range basically we'll use that function concatenate range and we'll pass it any range of data.
Now, what I'll have to do is.
I'll have to put the code for this in the blog entry.
So, if you go to "MrExcel.com" and click on podcast and the left side you'll be able to get to the blog, where you could actually copy this code from the blog, into the worksheet.
And then what we're going to do is, we're gonna use the concatenate range formula.
So, now we can just use equal concatenate range and give it the whole range of data that we want to concatenate.
Closing parenthesis and it puts everything together with a space much easier than going out notepad.
Clearly a great way to go.
Now, you know in almost 500 episodes of the podcast.
I've probably only switched over into VBA, maybe two or three times because my contention is, that it's much harder to try and explain VBA in a two minute podcast.
You can tell that we're already up to probably about three minutes, today.
But, it is amazing what you can do with Excel VBA.
Most of the people who hang out at, "MrExcel.com".
There are their VBA fans.
I'm certainly a VBA fan, a lot of power that you can use in VBA.
So, even though I don't talk about it in the podcast a lot.
It's really worth, while to learn VBA.
And there's some times when you just have a problem in Excel, where it is so much easier to knock out a couple lines of Code and it completely solves the problem as in this case.
So, thanks to Jonathan, from the UK.
I'll be sending one of my Excel master pins for this great suggestion and thanks for stopping by.
We'll see you next time for another net cast from MrExcel.
I'm Bill Jelen.
Today, we're gonna revisit, episode 578.
This is where we were trying to put a whole bunch of words together into a single cell, without having to build a concatenation formula.
And Jonathan from the UK wrote it and said, "Hey! I know you don't like to cover VBA in the podcast, but this quick user-defined function would really be that with the way to go." So, I'm gonna take advantage of Jonathan's email here and just point out that my book, 'VBA and Macros for Microsoft Excel', is now out for Excel 2007.
This is a basically the same book that we had for Excel 2003.
Along with the new features that are new in 2007.
So, if you've upgraded to 2007 or are thinking about upgrading, certainly go ahead and get that.
Sorting has changed, the new pivot tables, data visualizations.
We covered those items in Excel 2007 and again the book is designed for someone who knows Excel very very well.
But, wants to get up the VBA learning curve.
Maybe, you recorded a few macros.
Maybe they worked, they probably didn't work.
The book will tell you how to get around that.
So, Jonathan said, you know, rather than trying to go out to notepad.
Which is what I actually did here in episode 578.
It'd be much easier just to have a custom function to put these words together.
So, what we'll do is we'll hit [ alt+ F11 ].
That'll take us to the VBA editor.
We're gonna use insert module, to add a little bit of a little module.
Jonathan sent me the code.
It's for concatenate range basically we'll use that function concatenate range and we'll pass it any range of data.
Now, what I'll have to do is.
I'll have to put the code for this in the blog entry.
So, if you go to "MrExcel.com" and click on podcast and the left side you'll be able to get to the blog, where you could actually copy this code from the blog, into the worksheet.
And then what we're going to do is, we're gonna use the concatenate range formula.
So, now we can just use equal concatenate range and give it the whole range of data that we want to concatenate.
Closing parenthesis and it puts everything together with a space much easier than going out notepad.
Clearly a great way to go.
Now, you know in almost 500 episodes of the podcast.
I've probably only switched over into VBA, maybe two or three times because my contention is, that it's much harder to try and explain VBA in a two minute podcast.
You can tell that we're already up to probably about three minutes, today.
But, it is amazing what you can do with Excel VBA.
Most of the people who hang out at, "MrExcel.com".
There are their VBA fans.
I'm certainly a VBA fan, a lot of power that you can use in VBA.
So, even though I don't talk about it in the podcast a lot.
It's really worth, while to learn VBA.
And there's some times when you just have a problem in Excel, where it is so much easier to knock out a couple lines of Code and it completely solves the problem as in this case.
So, thanks to Jonathan, from the UK.
I'll be sending one of my Excel master pins for this great suggestion and thanks for stopping by.
We'll see you next time for another net cast from MrExcel.
