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

[blueeye]

Hi gals and guys,

here is my problem:

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

 

[blueeye]

This is it:

If Len(cell.Text) Then CatVisible = CatVisible & sSep & cell.Value2

Well, you have just saved my day. How can I help you or donate?

Thank you both for your exhausting help.

Regards,
blueeye

 

[shg]

I guess testing is occasionally warranted ...

Function CatVisible(r As Range, _
                    Optional sSep As String) As String
    Dim cell        As Range

    For Each cell In Intersect(r, r.Worksheet.UsedRange).SpecialCells(xlCellTypeVisible).Cells
        If Len(cell.Text) Then CatVisible = CatVisible & sSep & cell.Value2
    Next cell

    If Len(CatVisible) Then CatVisible = Mid(CatVisible, Len(sSep) + 1)
End Function

 

[blueeye]

@shg - Ye, sorry for my laziness. How much do I owe you for such a gorgeous help?

Thanks.

 

[shg]

You're welcome.

Cornelia de Lange Syndrome (CdLS) Foundation, Inc. -- give whatever you wish.

 

[blueeye]

Well, Thank you then. But I already do contribute regularly to another foundation. I just wanted to give you some bucks to "buy you a beer".
If you need any help with anything that I could handle myself, do not hesitate to contact me.

Thank you.

 

[blueeye]

@shg - I have found a new problem.
When I apply the filter and there is only one row in the output, I get the following error:

Run-time error '-2147417848 (80010108)':
Method 'Text' of object 'Range' failed

I use the following code and it allows the use of the button "SEND" only if the first row is not empty and also if there is only one row after the table's heading:

If ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then
   fruit = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Areas(2).Columns(1).Cells(1, 4)
ElseIf Not IsEmpty(Range("D4")) And Range("D3:D5").Rows.SpecialCells(xlCellTypeConstants).Count = 2 Then
   fruit = Range("D4")
Else
   MsgBox ("CHOOSE THE FRUIT")
   Exit Sub
End If

Don't you know where is the problem?

Thank you.

 

[blueeye]

Well, everything is working okay.
I was using:

dates = CatVisible(Range("O4:O65536").SpecialCells(xlCellTypeVisible), "%0A")

instead of

dates = CatVisible(Range("O4:O65536"), "%0A")

, cause the following code:

SpecialCells(xlCellTypeVisible)

is already contained in your CatVisible function.

I do apologize for any inconvenience or problems I have caused to you by the last post.

 

[shg]

No problems at all, good job.

 

[blueeye]

What if I were about to change the title a bit?

"How to Get Only Visible Rows (containing values) After Applying Filter" to:
"How to Get Visible & Hidden Rows (containing values) After Applying Filter"

Is there any way to use CatVisible function along with hidden rows as well?

Thank you.

 

[blueeye]

I have tried using CatVisible and ConcatenateRange functions. They both works properly unless the column width is set to 0px (hidden column).

Is there any way to get this function working along with hidden columns as well?

Thank you.