5
\$\begingroup\$

Purpose:
Given a Worksheet Table (ListObject) that may, or may not, have a filter currently applied to it, return a range consisting only of the filtered data.


Method:
Loop through each row in the table.
If row is visible, add it to a filteredRange via Union().
Return filteredRange.


Specific Areas of Interest:
Is the function name sufficiently intuitive?
Is there an easier way to achieve the desired results?
Are there edge cases I haven't accounted for?


Function:

Public Function GetFilteredTableRange(ByRef targetTable As ListObject, Optional ByVal includeHeaders As Boolean = False)
    '/ given a table, return a range object that contains only those rows which are visible
    '/ Do this by looping through tableRows, adding all visible rows to a unionRange

    Dim allDataRange As Range
    Set allDataRange = targetTable.DataBodyRange

    Dim filteredRange As Range
    Dim rowRange As Range

    If includeHeaders Then
        Set rowRange = targetTable.HeaderRowRange
        Set filteredRange = rowRange
    End If

    For Each rowRange In allDataRange.Rows()
        If rowRange.EntireRow.Hidden = False Then

            If filteredRange Is Nothing Then
                Set filteredRange = rowRange
            Else
                Set filteredRange = Union(filteredRange, rowRange)
            End If

        End If
    Next rowRange

    Set GetFilteredTableRange = filteredRange

End Function
\$\endgroup\$
6
\$\begingroup\$
Public Function GetFilteredTableRange(ByRef targetTable As ListObject, Optional ByVal includeHeaders As Boolean = False)
    '/ given a table, return a range object that contains only those rows which are visible
    '/ Do this by looping through tableRows, adding all visible rows to a unionRange

Newlines
are
cheap.

In Python, you always aim to keep your lines shorter than 80 characters long. I personally think it's a good practice to keep that size as a guideline in other languages as well. Your first line is 120, 1.5x my proposed maximum.

Added benefit of trying to stick with a maximum is it keeps you sharp. You're forced to think whether it's absolutely necessary for that line to be that long. Long lines are not fun to read, so try not to make them longer than necessary.

Public Function GetFilteredTableRange( _
    ByRef targetTable As ListObject, _
    Optional ByVal includeHeaders As Boolean = False _
)
    '/ Given a table, return a range object with only the visible rows.
    '/ Do this by looping through tableRows,
    '/ adding all visible rows to a unionRange.

That's how I'd do it, but I'm not up to par with VBA coding standards.

\$\endgroup\$
6
\$\begingroup\$
  • You should explicitly declare the return type of your function.
  • You can simplify your code just a tad:

       Dim rowRange As Range
    
       If includeHeaders Then
          Set rowRange = targetTable.HeaderRowRange
          Set filteredRange = rowRange
       End If
    
       For Each rowRange In allDataRange.Rows()
    

    Could be:

    If includeHeaders Then
        Set filteredRange = targetTable.HeaderRowRange
    End If
    
    Dim rowRange As Range
    For Each rowRange In allDataRange.Rows()
    

    This moves rowRange closer to it's first actual usage.

\$\endgroup\$
  • \$\begingroup\$ Good catch on the return type. \$\endgroup\$ – Kaz Jul 6 '16 at 15:33
3
\$\begingroup\$

The function name is not as intuitive as it could be.

Something like

GetVisibleTableRange()

is closer to what the function is actually doing (getting all the visible data), but doesn't mesh as well with the default option of excluding headers. The name implies getting the Table Range, rather than just the Data Range.

If you made the default behaviour including headers, that name would be just about perfect.

\$\endgroup\$
3
\$\begingroup\$

Is there an easier way to achieve the desired results?

Not sure if it'll fully fit your use case, but allDataRange.SpecialCells(xlCellTypeVisible) would return a range of only visible cells. Would seem to be more viable than needing to loop through every row of the range.

\$\endgroup\$
  • \$\begingroup\$ This is a good solution if you include error handling. Range.SpecialCells will throw an error if there are no cells of the specified type. \$\endgroup\$ – ChrisB Mar 12 '18 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.