CommaDelimit by Join(Application.Transpose visible range

[GAdamWTW]

I currently comma delimit using this and put it to the clipboard using MSForms.DataObject
Dim ConvertComma
ConvertComma = Join(Application.Transpose(Selection.Value), ", ")


Dim clipboard As MSForms.DataObject
Set clipboard = New MSForms.DataObject
clipboard.SetText ConvertComma
clipboard.PutInClipboard

The issue is that this includes hidden cells. When I attempt to fix this by adding SpecialCells(xlCellTypeVisible) in the Selection.value this creates multiple selections and I get an error.
I'm needing to either store the multiple ranges as 1 range without pasting it in the work sheet or to adjust the code to accept multiple selections.

I've attempted this which has allowed me to make multiple selections however it's pasting over existing data and only storing 1 of the selections.
Dim ConvertComma
Dim myarray As Variant
myarray = Selection
Selection.Value = myarray

ConvertComma = Join(Application.Transpose(myarray), ", ")

MSForms requires turning on features in the Library so if this isn't done that section can be replaced with a msg box for troubleshooting.

 

[JoeMo]

Welcome to the forum!

Maybe something like this:

Sub VisOnly()
Dim R As Range, Ar As Range, ConvertComma
On Error Resume Next
Set R = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not R Is Nothing Then
    If R.Areas.Count > 1 Then
        ReDim ConvertComma(1 To R.Areas.Count)
        For i = 1 To R.Areas.Count
            ConvertComma(i) = Join(Application.Transpose(R.Areas(i).Value), ", ")
        Next i
    Else
        ConvertComma = Join(Application.Transpose(R.Value), ", ")
    End If
End If
'code to do things with ConvertComma
End Sub

 

[GAdamWTW]

Hi Joe,
Thank you. I've been reading in the forms for a while but just joined to participate today.

Your suggestion does allow for multiple selections cause from the xlCellTypeVisible but fails with 2 conditions:
1. If any part of the visible range has only 1 row.
2. Storing the multiple selections for placement into clipboard. (For the CovertComma(i) what is the output variable which for the "Else" is just ConvertComma?
FYI, When it's a single "Area" it works like my original so this is a step in the right direction allowing the 2 possibilities to be worked separately.
I'm not use to working with someone like this so if I misunderstood something in your code my apologies.

The end result I'm trying for is to be able to select the visible range, run the macro, and paste the result manually wherever I want it.

 

[JoeMo]

Hi Joe,
Thank you. I've been reading in the forms for a while but just joined to participate today.

Your suggestion does allow for multiple selections cause from the xlCellTypeVisible but fails with 2 conditions:
1. If any part of the visible range has only 1 row.
2. Storing the multiple selections for placement into clipboard. (For the CovertComma(i) what is the output variable which for the "Else" is just ConvertComma?
FYI, When it's a single "Area" it works like my original so this is a step in the right direction allowing the 2 possibilities to be worked separately.
I'm not use to working with someone like this so if I misunderstood something in your code my apologies.

The end result I'm trying for is to be able to select the visible range, run the macro, and paste the result manually wherever I want it.

I've modified the code, but have not tested it so may need some tweaking. See comments in the code for where you need to add some lines to place the output into the clipboard.

Sub VisOnly()
Dim R As Range, Ar As Range, ConvertComma
On Error Resume Next
Set R = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not R Is Nothing Then
    If R.Areas.Count > 1 Then
        ReDim ConvertComma(1 To R.Areas.Count)
        For i = 1 To R.Areas.Count
            If R.Areas(i).Count = 1 Then
                ConvertComma(i) = R.Areas(i).Value
            Else
                ConvertComma(i) = Join(Application.Transpose(R.Areas(i).Value), ", ")
            End If
        Next i
        ConvertComma = Join(ConvertComma, ", ") '<--put this variable into the clipboard before the next Else
    Else
        If R.Count = 1 Then
            ConvertComma = R.Value
        Else
            ConvertComma = Join(Application.Transpose(R.Value), ", ")
        End If
        'put ConvertComma into clipboard before the next End If
    End If
End If
End Sub

 

[GAdamWTW]

Same issues though with the change it errors on "For i = 1..." when there is only 1 row as i being a variable which isn't set. If I dim it as an integer it resolves this but still breaks with any multiple selection for placing in the clipboard. I'd already added it where you pointed out. Here's where it's at now:

Sub zCommaD_VisOnly()
Dim R As Range, Ar As Range, ConvertComma
Dim i As Integer
Dim clipboard As MSForms.DataObject
On Error Resume Next
Set R = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0




If Not R Is Nothing Then
If R.Areas.Count > 1 Then
ReDim ConvertComma(1 To R.Areas.Count)
For i = 1 To R.Areas.Count
ConvertComma(i) = Join(Application.Transpose(R.Areas(i).Value), ", ")
Next i

Set clipboard = New MSForms.DataObject
clipboard.SetText ConvertComma = Join(ConvertComma, ", ")
clipboard.PutInClipboard

Else
ConvertComma = Join(Application.Transpose(R.Value), ", ")

Set clipboard = New MSForms.DataObject
clipboard.SetText ConvertComma
clipboard.PutInClipboard

End If
End If


End Sub

 

[JoeMo]

I'm not sure what I'm doing differently though yesterday when trying this it ran through just fine though today I'm getting an error with "For i = 1..." that i is "Variable not defined".
If adding "Dim i as Integer" it messes up i in "CovertComma(i)".

Can you explain what "messes up" means?

 

[GAdamWTW]

Sorry the error on clipboard.SetText ConvertComma = Join(ConvertComma, ", ") says "Type mismatch" "Run-time error '13':"

 

[GAdamWTW]

Ok so the multiple selections is fixed now.
The error is now only with if there is an area of the visible range which is only 1 row marked in red below.

Sub zCommaD_VisOnly()
Dim R As Range, Ar As Range, ConvertComma
On Error Resume Next
Set R = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
Dim i As Integer
Dim clipboard As MSForms.DataObject


If Not R Is Nothing Then
    If R.Areas.Count > 1 Then
        ReDim ConvertComma(1 To R.Areas.Count)
        For i = 1 To R.Areas.Count
[COLOR=#FF0000]            ConvertComma(i) = Join(Application.Transpose(R.Areas(i).Value), ", ")[/COLOR]
        Next i
        ConvertComma = Join(ConvertComma, ", ")
        
    Set clipboard = New MSForms.DataObject
    clipboard.SetText ConvertComma
    clipboard.PutInClipboard
    
    Else
        ConvertComma = Join(Application.Transpose(R.Value), ", ")
        
    Set clipboard = New MSForms.DataObject
    clipboard.SetText ConvertComma
    clipboard.PutInClipboard
    
        End If
End If


End Sub

 

[JoeMo]

Sorry the error on clipboard.SetText ConvertComma = Join(ConvertComma, ", ") says "Type mismatch" "Run-time error '13':"

That should be:

clipboard.SetText ConvertComma

the code I gave you has already defined ConvertComma.

 

[GAdamWTW]

Thank you JoeMo for all your help. It's working. In troubleshooting the piece I failed to put back the IF condition for only 1 row in the visible range.
Your changes work a minor tweak.

Here's the finished code: (Thank you again!)

Sub zCommaD_VisOnly()Dim R As Range, Ar As Range, ConvertComma
On Error Resume Next
Set R = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
Dim i As Integer
Dim clipboard As MSForms.DataObject




If Not R Is Nothing Then
    If R.Areas.Count > 1 Then
        ReDim ConvertComma(1 To R.Areas.Count)
        For i = 1 To R.Areas.Count
            If R.Areas(i).Count = 1 Then
                ConvertComma(i) = R.Areas(i).Value
            Else
                ConvertComma(i) = Join(Application.Transpose(R.Areas(i).Value), ", ")
            End If
        Next i
        ConvertComma = Join(ConvertComma, ", ")
        
    Set clipboard = New MSForms.DataObject
    clipboard.SetText ConvertComma
    clipboard.PutInClipboard
    
    Else
        ConvertComma = Join(Application.Transpose(R.Value), ", ")
        
    Set clipboard = New MSForms.DataObject
    clipboard.SetText ConvertComma
    clipboard.PutInClipboard
    
        End If
End If
End Sub