Excel: Excel VBA - How to Get Only Visible Rows (containing values) After Applying Filter


Hi gals and guys, here is my problem: [IMG]http://i57.tinypic.com/2d6uyaq.jpg[/IMG] ConcatenateRange Function:

Function ConcatenateRange(ByVal cell_range As Range, _                    Optional ByVal seperator As String) As String
Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long
cellArray = cell_range.Value
For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) <> 0 Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next
If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If
ConcatenateRange = newString
End Function
I would like to get ALL visible rows (containing values) after applying a filter, not only the row A4. I have tried the following code, but this only output the A4 or more exactly, the rows until the hidden ones.
AllVisibleRows = ConcatenateRange(Range("A4:A65536").SpecialCells(xlCellTypeVisible))
How could I get all those filtered rows (A4,A6,etc.)? Thank you in advance. Regards, blueeye


This question generated 15 answers. To proceed to the answers, click here.

This thread is current as of August 20, 2014.


For more resources for Microsoft Excel