VBA TO TEXTJOIN VISIBLE CELLS ONLY

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
210
Office Version
  1. 365
Platform
  1. Windows
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,
 
PeterSS,

This is great. Not having to have a code for this function. It worked perfectly.

I updated my account details.

Thank you so much!
Juicy
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
This is great. Not having to have a code for this function. It worked perfectly.
Great news!
.. and thanks for updating your profile. :)


@Dan_W
I just noticed that you are using an old version of XL2BB. I suggest that you update to the latest version - see my signature block below.
 
Upvote 0
Rich (BB code):
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
Dan,

You might find it easier to move the delimiter in the blue highlighted line before the value being concatenated as that will make the red highlighted line simpler (it can use the Mid function and omit the optional third argument). Here is what you code would look like when modified this way...
VBA Code:
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 & sptChar & rg.Value
        End If
    Next
    ConcatenateVisible = Mid(ConcatenateVisible, Len(sptChar) + 1)
End Function
 
Upvote 0
Great news!
.. and thanks for updating your profile. :)


@Dan_W
I just noticed that you are using an old version of XL2BB. I suggest that you update to the latest version - see my signature block below.
Thank you, Peter. It's a great add-in, and makes it easier to use the forum!
 
Upvote 0
Dan,

You might find it easier to move the delimiter in the blue highlighted line before the value being concatenated as that will make the red highlighted line simpler (it can use the Mid function and omit the optional third argument). Here is what you code would look like when modified this way...
VBA Code:
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 & sptChar & rg.Value
        End If
    Next
    ConcatenateVisible = Mid(ConcatenateVisible, Len(sptChar) + 1)
End Function
Thank you, Rick. I just tried it out, because I couldn't quite figure why a MID function would be used - I worked my way through it, and now I understand. Very clever and, far less convoluted than the original script. Thank you for the tip.
 
Upvote 0
You are quite welcome. The ability to be able to omit the third argument to the VBA Mid function in order to retrieve the remainder of the text starting at the specified starting character position is extremely useful... I only wish Microsoft would have implemented that functionality with the Excel MID function as well.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top