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]

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?

Let's say the you have this in Column C....

C2: One
C3: Two Three
C4: Four

After these three cells are assigned to strMaker... if we printed out the contents of strMake somewhere, what would that print out look like?

 

[Marcelo Branco]

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

The SpecialCells method returns a Range Object, so trying to assign a String type variable to a Range causes the error.

To get the first visible cell try something like

Dim rVisible As Range, strMaker As String

Set rVisible = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
strMaker = rVisible.Cells(1)
MsgBox strMaker

M.

 

[PatOBrien198]

Normally, you'd need to dim the array variable as a Variant.
However, this assignment seems to work only with contiguous cells. I couldn't get it to work with the visible cells only.

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

ReDim strMaker(0)
For Each rng In Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
ReDim Preserve strMaker(UBound(strMaker) + 1)
strMaker(UBound(strMaker)) = rng
Next

'check results
For x = 1 To UBound(strMaker)
Debug.Print strMaker(x)
Next
End Sub

 

[dougdrex]

The SpecialCells method returns a Range Object, so trying to assign a String type variable to a Range causes the error.

To get the first visible cell try something like

Dim rVisible As Range, strMaker As String

Set rVisible = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
strMaker = rVisible.Cells(1)
MsgBox strMaker

M.

What is the (1) for after the rVisible.Cells? Can I use the "rVisible.Cells" be used without it?

 

[Rick Rothstein]

What is the (1) for after the rVisible.Cells? Can I use the "rVisible.Cells" be used without it?

I had kind of hoped you would have answered the question I asked in Message #2 by now. I am still having trouble determining if you want a text string (which your original posting suggested) or an array... can you clarify this please? And, if you want a text string instead of an array, please answer the question I asked in Message #2 .

 

[Marcelo Branco]

Cells(1) designates the first cell in a range.
For example:
Range("C2:C4").Cells(1) --> C2
Range("C2:C4").Cells(2) --> C3
and so on

M.

 

[dougdrex]

Let's say the you have this in Column C....

C2: One
C3: Two Three
C4: Four

After these three cells are assigned to strMaker... if we printed out the contents of strMake somewhere, what would that print out look like?

Sorry, Rick - I had a number of issues I was trying to address at the same time and got caught up with different possible solutions. Below is the response to your query.

C2 = AB12345
C3 = ZY98765
C4 = CD67890

As you can tell, the cell values will be two alphabetic characters followed by five numeric characters. I hope that helps. Please let me know if you have any ideas.


Thanks!
Doug

 

[Rick Rothstein]

C2 = AB12345
C3 = ZY98765
C4 = CD67890

As you can tell, the cell values will be two alphabetic characters followed by five numeric characters. I hope that helps. Please let me know if you have any ideas.

Okay, that tells me what is in the cells, but it doesn't tell me what you want in the strMaker variable. Is that variable really a text string variable? If so, what text is being assigned to it? In other words, what delimiter would you want between each cell value? For example, if you wanted a comma followed by a space between them, then a code line like this...

MsgBox strMaker

would display this...

AB12345, ZY98765, CD67890

Or did you actually want strMaker to be an array (which I think everyone else is assuming) where you can pick out any one value by supplying the variable with a number index (in parentheses)?

 

[dougdrex]

Okay, that tells me what is in the cells, but it doesn't tell me what you want in the strMaker variable. Is that variable really a text string variable? If so, what text is being assigned to it? In other words, what delimiter would you want between each cell value? For example, if you wanted a comma followed by a space between them, then a code line like this...

MsgBox strMaker

would display this...

AB12345, ZY98765, CD67890

Or did you actually want strMaker to be an array (which I think everyone else is assuming) where you can pick out any one value by supplying the variable with a number index (in parentheses)?

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!