Assign visible cells in range to variable

[dougdrex]

Using Excel 2010

I'm trying to assign the visible cells in a range to a variable after having filtered the data. However, I'm getting a Run-time error '13': Type Mismatch error.

Following is the pertinent code for the variable:

    Dim strMaker As String
    strMaker = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).SpecialCells(xlCellTypeVisible)

I've been searching and trying possible solutions for several hours, but to no avail. Does anyone have any clues what I'm doing wrong? If it helps, the values in the cells to be assigned to the variable will end up being included in the "Cc" field of an Outlook email.


Thanks!
Doug

 

[PatOBrien198]

Yes, instead of doing the replacing of spaces with the semi colon at the end of the function,
add the semi colon when the cell.value is added inside the loop.

 

[dougdrex]

Because I cannot duplicate the problem you have and since you cannot share your workbook, I am afraid I will not be able to help you any further. I will let Pat modify his code to handle the new problem you have identified.

Rick, thank you all your help over the last few days. Your expertise and willingness to share knowledge is really appreciated!

 

[PatOBrien198]

GetVisibleMaker = GetVisibleMaker & " " & Cell.Value
should be:
GetVisibleMaker = GetVisibleMaker & "; " & Cell.Value

and delete the line that does the replacing of the " " with ";" at the end of the function.

 

[dougdrex]

Yes, instead of doing the replacing of spaces with the semi colon at the end of the function,
add the semi colon when the cell.value is added inside the loop.

That didn't quite work because the distribution lists (that contain spaces) were still divided up with semicolons.

The below code works great except it inserts a semicolon before the first value which causes an error.

Function GetVisibleStrDL(rngStrDL As Range, Optional Delim As String = "; ") As String    Dim strStrDL As String, Cell As Range
    For Each Cell In rngStrDL
        If Cell.EntireRow.Hidden = False And InStr(GetVisibleStrDL, Cell.Value) = 0 Then
            GetVisibleStrDL = GetVisibleStrDL & Delim & Cell.Value
        End If
    Next
End Function

Is there a way the code can abstain from inserting the semicolon before the first value? Or is there a better solution from the previous set of function code?

 

[dougdrex]

The code below works great except it inserts a semicolon (";") before the first email address which creates an error.

Function GetVisibleStrDL(rngStrDL As Range, Optional Delim As String = "; ") As String    Dim strStrDL As String, Cell As Range
    For Each Cell In rngStrDL
        If Cell.EntireRow.Hidden = False And InStr(GetVisibleStrDL, Cell.Value) = 0 Then
            GetVisibleStrDL = GetVisibleStrDL & "; " & Cell.Value
        End If
    Next
End Function

strDL = GetVisibleStrDL(Range("E2", Cells(Rows.Count, "E").End(xlUp)))

Is there a way for the code to avoid inserting the semicolon before the first value? Or is there some other solution that is easier?

 

[PatOBrien198]

Good one... I didn't think of that....
Test for length of GetVisibleStr

GetVisibleStrDL = GetVisibleStrDL & IIf(Len(GetVisibleStrDL) > 0, Delim, "") & Cell.Value

 

[Rick Rothstein]

Good one... I didn't think of that....
Test for length of GetVisibleStr

GetVisibleStrDL = GetVisibleStrDL & IIf(Len(GetVisibleStrDL) > 0, Delim, "") & Cell.Value

I think it might be easier, and slightly more efficient, to leave your original code as is and remove the leading delimiter (which you know is there) after the loop has finished...

GetVisibleStrDL = Mid(GetVisibleStrDL, Len(Delim) + 1)

 

[PatOBrien198]

Yes. Absolutely.

 

[dougdrex]

I think it might be easier, and slightly more efficient, to leave your original code as is and remove the leading delimiter (which you know is there) after the loop has finished...

GetVisibleStrDL = Mid(GetVisibleStrDL, Len(Delim) + 1)

The code passed along a blank string to the variable. There are three visible cells with three unique values that should be passed along to the string variable.

I'm wondering if the new line of code is in the wrong place. Below is the code.

Function GetVisibleStrDL(rngStrDL As Range, Optional Delim As String = "; ") As String    Dim strStrDL As String, Cell As Range
    For Each Cell In rngStrDL
        If Cell.EntireRow.Hidden = False And InStr(GetVisibleStrDL, Cell.Value) = 0 Then
            GetVisibleStrDL = Mid(GetVisibleStrDL, Len(Delim) + 1)
        End If
    Next
End Function

strDL = GetVisibleStrDL(Range("E2", Cells(Rows.Count, "E").End(xlUp)))

 

[PatOBrien198]

Function GetVisibleStrDL(rngStrDL As Range, Optional Delim As String = "; ") As String
Dim strStrDL As String, Cell As Range
    For Each Cell In rngStrDL
        If Cell.EntireRow.Hidden = False And InStr(GetVisibleStrDL, Cell.Value) = 0 Then
            GetVisibleStrDL = GetVisibleStrDL & Delim & Cell.Value
        End If
    Next
    GetVisibleStrDL = Mid(GetVisibleStrDL, Len(Delim) + 1)
End Function