VBA TO TEXTJOIN VISIBLE CELLS ONLY

[JuicyMusic]

I've found a code that was supposed to Text Join visible cells only but it did'nt work or i did something wrong.

Goal: To join visible cells in a column and join them with a semi colon in between each. I am joining email addresses that are visible based on what has been filtered. To create a multiple email string.

1) Would need the code to join email addresses in column B, for example.

2) Would like the code to combine the visible filtered cells in any column that a user places the cursor on. Active cell would be ideal if possible, just in case i have a spreadsheet where the email addresses are in another column......and if possible, to make the code recognize that the active column does not contain email addresses.

2.5) A message box that says "The column you've chosen does not contain email addresses. Try again". With "Okay". Something like that.

3) If employee names are in column A and email addresses are in column B, then i would like the code to produce a result in a cell that is 3 rows above the header of column B.

4) The header row is on row 5, the employee names and addresses list starts on row 6. Would like the results in row 3.

5) Would like a command button if necesarry. Disregard if the TextJoin function will automatically be generated after the filter has been set. I don't know how this part works.

Result in row 3
Header is in row 5
Email list in excel starts in row 6.

Thank you so much.

Juicy,

 

[Dan_W]

TEXTJOIN isn't VBA, it's a worksheet function (or formula, depending on who you ask). If you would like help understanding how to use worksheet functions, there are plenty of very useful resource (like ExcelJet, My Excel Online, and Spreadsheeto).

But if you've already found code which didn't work for whatever reason, why not start there? What code did you use and what was sample data and the problematic output? If it's a simple fix, that will you a lot of time trying to work out how to write the code.

 

[JuicyMusic]

TEXTJOIN isn't VBA, it's a worksheet function (or formula, depending on who you ask). If you would like help understanding how to use worksheet functions, there are plenty of very useful resource (like ExcelJet, My Excel Online, and Spreadsheeto).

But if you've already found code which didn't work for whatever reason, why not start there? What code did you use and what was sample data and the problematic output? If it's a simple fix, that will you a lot of time trying to work out how to write the code.

Thank you for responding. I have used TextJoin before but needed something in addition to the joining.

I need to have only the visible cells to be joined after a filter. I will show you the code i found online on Monday when i go to work. I will not forget.

In the mean time i will visit the sites you've suggested. I know these sites but will try again.

Merci,

Juicy

 

[JuicyMusic]

TEXTJOIN isn't VBA, it's a worksheet function (or formula, depending on who you ask). If you would like help understanding how to use worksheet functions, there are plenty of very useful resource (like ExcelJet, My Excel Online, and Spreadsheeto).

But if you've already found code which didn't work for whatever reason, why not start there? What code did you use and what was sample data and the problematic output? If it's a simple fix, that will you a lot of time trying to work out how to write the code.

I went to find the code that i had tried to use. Here is the link to the exact page. I'm on my Nook and dont know how to take a snapshot of the code,

How to concatenate only visible cells in Excel?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com

I hope this is acceptable for me to leave a link here. Let me know.

Merci,
Juicy

 

[Dan_W]

Hi. Thank you so much for sending that through. I now understand what you're after, and I think I know why it is that you weren't able to get the code to work.

It's a very useful script, to be honest, but the version of it that people are supposed to copy and paste has something in it which (if you didn't know how to 'fix' it) would prevent you from being able to use it.

Function ConcatenateVisible(xRg As Variant, sptChar As String)
'Updateby Extendoffice 20160922
    Dim rg As Range
    For Each rg In xRg
        If (rg.EntireRow.Hidden = False) And (rg.EntireColumn.Hidden = False) Then
            ConcatenateVisible = ConcatenateVisible & rg.Value & sptChar
        End If
    Next
    ConcatenateVisible = Left(ConcatenateVisible, Len(ConcatenateVisible) - Len(sptChar))
End Function

Try using this on filtered in a cell on a filtered column. I just checked it, and it worked for me on a filtered dummy data set I prepared.

Book2
	A	B	C	D	E
1	First Name	Last Name	Company	Email Address
2	Jenny	Jacobson	A Co	j.jacobson@aco.com
9	Dan	Wilkinson	A Co	d.wilkinson@aco.com
10	Darren	Ishimura	A Co	d.ishimura@aco.com
11	Alex	Tolstoy	A Co	a.tolstoy@aco.com
12
13	j.jacobson@aco.com; d.wilkinson@aco.com; d.ishimura@aco.com; a.tolstoy@aco.com
14
Sheet2

Cell Formulas
Range	Formula
A13	A13	=ConcatenateVisible(D2:D11,"; ")

 

[JuicyMusic]

Dan, thank you for responding so quickly.

This is exactly what i needed. I am going to compare the code in the link againts what you provided me. I am always wanting to learn more regarding how to tweak a code and how to identify what might be wrong with a code that i found online.

I will try this tomorrow at work and let you know that my need was resolved.

Merci,
Juicy

 

[Dan_W]

I know it doesn't answer all of your points. Points 2 and 2.5 seem fairly straight forward, and I don't know that I entirely understand the part about the command button. But try the code and let me know if you have any problems with it.

 

[JuicyMusic]

Hi Dan, I mentioned the command button since this was a macro. I was'nt sure before if this type of code required the act of executing a macro. Looks like it is automatic.

I would love to know what this type of code is called. The ones that run automatically.

You actually addressed all my points. I was actually just trying to give as much detail as possible. Better that than being vague.

I don't mind the formula at the bottom of the list. It seems that i can put this formula anywhere as long as the formula looks at the colunn with email addresses.

Can't wait to go to work tomorrow.

MERCI,

JUICY

 

[JuicyMusic]

Good morning Dan,

I added the code you left for me and set the ConcateVisible formula on my spreadsheet. It worked very well!

For example, I filtered for 5 email addresses from a email list which has 136 people on it. The formula brings back those 5 email addresses (that are visible after the filter has been set), and leaves a bunch of ";" at the end of the string. This is not a problem at all

I set the formula then deleted the code just to see what would happen. The formula wouldn't work and gave me a NAME error. I'm glad to know this now.

How do I tag this thread as RESOLVED?


Thank you so much.

Juicy

 

[Peter_SSs]

How do I tag this thread as RESOLVED?

Just by saying something like that is fine.

I have used TextJoin before but needed something in addition to the joining.

I need to have only the visible cells to be joined after a filter.

In case you are interested, I think you can get the list of email addresses with the TEXTJOIN worksheet function.
Depending on your version of Excel** you may need to confirm this formula with Ctrl+Shift+Enter, not just Enter, to get it to work properly.

** I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) (as well as platform) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

JuicyMusic 2020-06-17 1.xlsm
	A	B
1
2
3		Tom@somewhere;Ann@somewhere;Kim@somewhere
4
5	Name	Email
6	Tom	Tom@somewhere
7	Ann	Ann@somewhere
11	Kim	Kim@somewhere
12
Sheet1

Cell Formulas
Range	Formula
B3	B3	=TEXTJOIN(";",1,IF(SUBTOTAL(3,OFFSET(B6,ROW(B6:B11)-ROW(B6),,1)),B6:B11,""))