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]

It needs to have a semicolon (";") between each value because those values will be passed to the "Cc" field of an outgoing email message. I hope that helps!

Here is a function that can be called either from other VB code or used as a UDF (user defined function) in a worksheet cell formula which will return the text in the visible cells delimited by the optional Delim argument (defaulted to a semi-colon, so you can omit it if you want, or change if it desired). The required first argument is a validly specified range. In your case that would be...

Range("C2", Cells(Rows.Count, "C").End(xlUp))

So, once you place the code below in a standard Module, you can assign the values you want to the strMaker variable like this...

strMaker = GetVisible(Range("C2", Cells(Rows.Count, "C").End(xlUp)))

from within your own VBA code. Alternately, you could place the value in a cell using this formula (where you would specify the correct range for your data)...

=GetVisible(C2:C10)

Here is the function code...

[table="width: 500"]
[tr]
	[td]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 Then GetVisible = GetVisible & " " & Cell.Value
  Next
  GetVisible = Replace(Application.Trim(GetVisible), " ", Delim)
End Function[/td]
[/tr]
[/table]

 

[PatOBrien198]

So, you may need to do it in a kind of brute force method - as follows....

Sub MakeStringArray()
Dim x As Integer
Dim strMaker As String
Dim rng As Range

strMaker = ""
For Each rng In Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
strMaker = strMaker & "; " & rng.value
Next

'test result
debug.print strMaker

End Sub

 

[dougdrex]

Here is a function that can be called either from other VB code or used as a UDF (user defined function) in a worksheet cell formula which will return the text in the visible cells delimited by the optional Delim argument (defaulted to a semi-colon, so you can omit it if you want, or change if it desired). The required first argument is a validly specified range. In your case that would be...

Range("C2", Cells(Rows.Count, "C").End(xlUp))

So, once you place the code below in a standard Module, you can assign the values you want to the strMaker variable like this...

strMaker = GetVisible(Range("C2", Cells(Rows.Count, "C").End(xlUp)))

from within your own VBA code. Alternately, you could place the value in a cell using this formula (where you would specify the correct range for your data)...

=GetVisible(C2:C10)

Here is the function code...

[TABLE="width: 500"]
<tbody>[TR]
[TD]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 Then GetVisible = GetVisible & " " & Cell.Value
  Next
  GetVisible = Replace(Application.Trim(GetVisible), " ", Delim)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick,

Your solution looks like it will work best with the macro I'm building. But I do have one more question. I was using the strMaker variable to pass along the end result to the "Cc" field of an outgoing email message, but I don't understand how that variable is being used in the function code you provided. Can you explain and/or provide instructions for passing it to the "Cc" field? This beast is nearly finished, but I'm struggling with this last piece!

 

[dougdrex]

Please ignore my last question. I read your response again and saw the answer!

 

[dougdrex]

Hi Rick,

I have applied the Function code to the different columns where the macro needs to capture the values to insert into the "Cc" field. However, it only captures the last value in the list of visible cells. Below is the code I ended up with:

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 Then GetVisibleMaker = GetVisible & " " & Cell.Value
  Next
  GetVisibleMaker = Replace(Application.Trim(GetVisibleMaker), " ", Delim)
End Function

strMaker = GetVisibleMaker(Range("C2", Cells(Rows.Count, "C").End(xlUp)))

 

[PatOBrien198]

If Cell.EntireRow.Hidden = False Then GetVisibleMaker = GetVisible & " " & Cell.Value


should be:

If Cell.EntireRow.Hidden = False Then GetVisibleMaker = GetVisibleMaker & " " & Cell.Value



"GetVisible" should be "GetVisibleMaker"

 

[Rick Rothstein]

Hi Rick,

I have applied the Function code to the different columns where the macro needs to capture the values to insert into the "Cc" field. However, it only captures the last value in the list of visible cells. Below is the code I ended up with:

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 Then GetVisibleMaker = [B][COLOR="#FF0000"]GetVisible[/COLOR][/B] & " " & Cell.Value
  Next
  GetVisibleMaker = Replace(Application.Trim(GetVisibleMaker), " ", Delim)
End Function

strMaker = GetVisibleMaker(Range("C2", Cells(Rows.Count, "C").End(xlUp)))

When you changed the name of my function from GetVisible to GetVisibleMaker, you missed changing one occurrence of the function name (shown in red above).

 

[dougdrex]

If Cell.EntireRow.Hidden = False Then GetVisibleMaker = GetVisible & " " & Cell.Value


should be:

If Cell.EntireRow.Hidden = False Then GetVisibleMaker = GetVisibleMaker & " " & Cell.Value



"GetVisible" should be "GetVisibleMaker"

That worked perfectly, thank you!

And thank you to everyone who helped. I'm finishing up the final details on the macro right now!


Doug

 

[dougdrex]

Here is the function code...

[TABLE="width: 500"]
<tbody>[TR]
[TD]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 Then GetVisible = GetVisible & " " & Cell.Value
  Next
  GetVisible = Replace(Application.Trim(GetVisible), " ", Delim)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

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. Is there a simple solution to make this code only pick up the unique values that are visible? I'm getting quite a few duplicates.

Thanks!
Doug

 

[PatOBrien198]

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