CODEWITHSUNDEEP
vbaexcelfiltering
urdearboy
urdearboy

Reputation: 14580

Difference between filtering range and table

Can someone explain why there is a difference between filtering a range and a table? I get the difference between the two, but I dont see why a range filter cannot filter the underlying data when its formatted as a table.

I've been having issues filtering files due to the difference. I am consolidating x number of user files with the same headers. However, some people (thanks a lot, Charles across the hall) decide to format as a table which is causing issues.

Is there a universal way (or more effective way) to filter data that is independent of the format? As an example, I am trying to filter for text "NORAM" in Col A, and then copy/paste visible cells elsewhere.

Do I need to do something like this? 

If Range("A1") is range Then
    Filter Range("A1") with range method ("NORAM")
    Copy Visible Cells
    Paste to Destination
Else
    Filter Range("A1") with table method ("NORAM")
    Copy Visible Cells
    Paste to Destination
End If

Or is there a way to do something like this, which is independent of range or table? 

Range("A1") filter range regardless of format ("NORAM")
Copy Visible Cells
Paste to Destination

I do not need help copying or pasting data. The above are just generic steps. I'm hoping to see the most effective way to filter a range, a table, and hopefully a way to filter both regardless of format.

Upvotes: 1

Views: 251

Answers (1)

BigBen
BigBen

Reputation: 50008

I'm not sure what error you're getting but here's a quick filter, tested on both a table (Sheet1) and a range not formatted as a table (Sheet2). 

Option Explicit

Private Sub FilterTableOrRange(ws As Worksheet)
    Dim lastRow As Long, lastCol As Long

    With ws
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        .Range(.Cells(1, 1), .Cells(lastRow, lastCol)).AutoFilter _
            Field:=1, Criteria1:="NORAM"
    End With
End Sub

Sub Master()
    FilterTableOrRange Sheets("Sheet1")
    FilterTableOrRange Sheets("Sheet2")
End Sub

enter image description here

If this is not helpful, then please specify the exact issues you've had.

Upvotes: 1

Related Questions