Auto Filtered Range into msgbox?

[supdawg]

Been stuck on this for a while now. Far too long for something this simple.

I'm just trying to copy the contents of an autofiltered range into a msgbox.

This is what I was trying to get to work:

 MsgBox Join(Application.WorksheetFunction.Transpose(Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value), Chr$(10))

^^ This works, but gives me a msgbox with all of the range's cell's contents. I just need the ones which are visible.

I've tried to insert .SpecialCells(xlCellTypeVisible) into the code, but it doesn't work the way I expect it to.

Any thoughts, i am sure its something simple as always.

 

[Peter_SSs]

Try this. It would need an error check if it is possible that there will be no visible cells in the range.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> FilteredDataToMsgBox()<br>    <SPAN style="color:#00007F">Dim</SPAN> Vis <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Vals <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Vis = Range("B4", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Vis<br>        Vals = Vals & Chr(10) & c.Value<br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    <br>    MsgBox Replace(Vals, Chr(10), "", 1, 1)<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[supdawg]

Thanks Peter...

Works wonderfully well! One small question, though. There might be occasions where the range would have duplicated data, and I would really only like to return the set of unique values in that msgbox.

Could that be easily done by making a small modification to your code? Or would it be much more complicated?

 

[Peter_SSs]

There might be occasions where the range would have duplicated data, and I would really only like to return the set of unique values in that msgbox.

Not sure if this is the best way but I think it does what you want.


<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> FilteredDataToMsgBox()<br>    <SPAN style="color:#00007F">Dim</SPAN> Vis <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Vals <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Val <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Vis = Range("B4", Range("B" & Rows.Count).End(xlUp)) _<br>        .SpecialCells(xlCellTypeVisible)<br><br>    <SPAN style="color:#00007F">With</SPAN> CreateObject("Scripting.Dictionary")<br>        .CompareMode = vbTextCompare<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Vis<br>            Val = c.Value<br>            <SPAN style="color:#00007F">If</SPAN> Len(Val) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> .Exists(Val) <SPAN style="color:#00007F">Then</SPAN><br>                    Vals = Vals & Chr(10) & Val<br>                    .Add Val, Val<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>   <br>    MsgBox Replace(Vals, Chr(10), "", 1, 1)<br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[supdawg]

Thank you so much!