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

 

[Rick Rothstein]

Over the last few days, I've been trying to figure out how to make this function only pick up unique values that are visible, but have been unsuccessful.

What exactly do you mean by this? Do you want to list values that only appear once in the range (omitting values if they appear two or more times in visible cells) or do you want all the visible numbers listed only once no matter how many times they actually appear in visible cells? If the latter...

[table="width: 500"]
[tr]
	[td]Function GetUniqueVisible(Rng As Range, Optional Delim As String = ";") As String
  Dim Cell As Range
  With CreateObject("Scripting.Dictionary")
    For Each Cell In Rng
      If Cell.EntireRow.Hidden = False Then .Item(Cell.Value) = 1
    Next
    GetUniqueVisible = Replace(Application.Trim(Join(.Keys)), " ", Delim)
  End With
End Function[/td]
[/tr]
[/table]

 

[dougdrex]

Function GetVisible(Rng As Range, Optional Delim As String = ";") As String
Dim strMaker As String, Cell As Range
For Each Cell In Rng
If Cell.EntireRow.Hidden = False _
And InStr(GetVisible, Cell.Value) = 0 Then
GetVisible = GetVisible & " " & Cell.Value
End If
Next
GetVisible = Replace(Application.Trim(GetVisible), " ", Delim)
End Function

That didn't quite work. It still passed duplicate values to the string variable. Below is the code I've used.

Function GetVisibleMaker(rngMaker As Range, Optional Delim As String = "; ") As String
  Dim strMaker As String, Cell As Range
  For Each Cell In rngMaker
    If Cell.EntireRow.Hidden = False And InStr(GetVisible, Cell.Value) = 0 Then
        GetVisibleMaker = GetVisibleMaker & " " & Cell.Value
    End If
  Next
  GetVisibleMaker = Replace(Application.Trim(GetVisibleMaker), " ", Delim)
End Function

    strMaker = GetVisibleMaker(Range("B2", Cells(Rows.Count, "B").End(xlUp)))

In this particular instance, it is passing three identical values to the string variable. Any ideas?

 

[Rick Rothstein]

That didn't quite work. It still passed duplicate values to the string variable.

You did not answer the question I asked in Message #21 , so let me try again...

Do you want to list values that only appear once in the range (omitting values if they appear two or more times in visible cells) or do you want all the visible numbers listed only once no matter how many times they actually appear in visible cells?

 

[dougdrex]

What exactly do you mean by this? Do you want to list values that only appear once in the range (omitting values if they appear two or more times in visible cells) or do you want all the visible numbers listed only once no matter how many times they actually appear in visible cells? If the latter...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function GetUniqueVisible(Rng As Range, Optional Delim As String = ";") As String
  Dim Cell As Range
  With CreateObject("Scripting.Dictionary")
    For Each Cell In Rng
      If Cell.EntireRow.Hidden = False Then .Item(Cell.Value) = 1
    Next
    GetUniqueVisible = Replace(Application.Trim(Join(.Keys)), " ", Delim)
  End With
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick,

I'm wanting all the visible string values to be listed only once no matter how many times they appear in the visible cells. I used the code you provided (adjusted for my particular needs), but it passed an empty value to the variable. Below is the code in my sub:

Function GetVisibleMaker(rngMaker As Range, Optional Delim As String = "; ") As String
    Dim strMaker As String, Cell As Range
    With CreateObject("Scripting.Dictionary")
        For Each Cell In rngMaker
            If Cell.EntireRow.Hidden = False Then .Item(Cell.Value) = 1
        Next
        GetUniqueVisible = Replace(Application.Trim(Join(.Keys)), " ", Delim)
    End With
End Function


Function GetVisibleStrDL(rngStrDL As Range, Optional Delim As String = "; ") As String
    Dim strStrDL As String, Cell As Range
    With CreateObject("Scripting.Dictionary")
        For Each Cell In rngStrDL
            If Cell.EntireRow.Hidden = False Then .Item(Cell.Value) = 1
        Next
        GetUniqueVisible = Replace(Application.Trim(Join(.Keys)), " ", Delim)
    End With
End Function

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

It's probably a minor detail, but I reviewed everything and couldn't find where I'm going wrong.

 

[Rick Rothstein]

Hi Rick,

I'm wanting all the visible string values to be listed only once no matter how many times they appear in the visible cells. I used the code you provided (adjusted for my particular needs), but it passed an empty value to the variable.

Do you mean you got two delimiters next to each other with nothing between them? If so, I cannot duplicate that whether the cell is totally blank or if it contains only blank spaces (one or more). That leads me to believe you have something in the cell you think is blank. For example, if you copied your values from the web somewhere, there is the possibility that you picked up some "non-breaking spaces" (ASCII 160)... these display as blanks on the screen, but that are not true blanks (ASCII 32) and my routine would consider them non-blank characters and reserve a spot for them. So, you should check the cells you think are blank to see if they are truly blank. You could do this with the LEN function in a cell formula. Let's say your blank looking cell is E5, just put this in a blank cell somewhere and if it doesn't say 0, you have something in the cell...

=LEN(E5)

 

[dougdrex]

Do you mean you got two delimiters next to each other with nothing between them? If so, I cannot duplicate that whether the cell is totally blank or if it contains only blank spaces (one or more).

Hi Rick,

In this particular instance, there are three values that should be picked up by the function code. All three are the same, AB12345. Instead of returning "AB12345; AB12345; AB12345", I'm trying to get code that will just return "AB12345". The way it is currently working is that it is returning "" (blank).

In some instances, there will be many more values, with some of them containing duplicates and others having a single instance of the value. They need to be able to return a series of string values that will look something like this: "AB12345; CD67890; EF09876; GH54321". I hope my explanation is not confusing.

 

[PatOBrien198]

Sorry about that....

InStr(GetVisible, Cell.Value) = 0 Then

should be:

InStr(GetVisibleMaker, Cell.Value) = 0 Then

 

[Rick Rothstein]

Hi Rick,

In this particular instance, there are three values that should be picked up by the function code. All three are the same, AB12345. Instead of returning "AB12345; AB12345; AB12345", I'm trying to get code that will just return "AB12345". The way it is currently working is that it is returning "" (blank).

In some instances, there will be many more values, with some of them containing duplicates and others having a single instance of the value. They need to be able to return a series of string values that will look something like this: "AB12345; CD67890; EF09876; GH54321". I hope my explanation is not confusing.

I cannot duplicate the problem here on my copy of Excel... I get the output you show you want. Can you post a copy of the workbook that this is occurring on to DropBox or some other such free file sharing facility so I can test/debug my code directly against your data?

 

[dougdrex]

Sorry about that....

InStr(GetVisible, Cell.Value) = 0 Then

should be:

InStr(GetVisibleMaker, Cell.Value) = 0 Then

Hi Rick,

Because the industry I'm in is heavily regulated, I am not allowed to share the data.

However, Pat's solution works perfectly except for one detail that I did not account for. One of the columns containing values that go into the "Cc" field has distribution lists that contain spaces within the name. This is causing problems. For example, "*ABC Risk and Control Canada" is translated into "*ABC; Risk; and; Control; Canada", thereby creating unrecognizable email addresses. So it inserts the semicolons where each space is.

Is there a solution for this issue?

 

[Rick Rothstein]

Hi Rick,

Because the industry I'm in is heavily regulated, I am not allowed to share the data.

However, Pat's solution works perfectly except for one detail that I did not account for. One of the columns containing values that go into the "Cc" field has distribution lists that contain spaces within the name. This is causing problems. For example, "*ABC Risk and Control Canada" is translated into "*ABC; Risk; and; Control; Canada", thereby creating unrecognizable email addresses. So it inserts the semicolons where each space is.

Is there a solution for this issue?

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.