# Get Filtered Data Range in a Table

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


## 4 Answers

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.

• 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.

• Good catch on the return type. – Kaz Jul 6 '16 at 15:33

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.

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.

• 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. – ChrisB Mar 12 '18 at 19:46