Get Filtered Data Range in a Table

Question

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

Answer 1

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.

Answer 2

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

• Kaz: Good catch on the return type.

Answer 3

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.

Answer 4

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.

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