Is there a native excel class which allows a range to be copied and sorts/filters applied?

Question

I have a range I'd like to arbitrarily sort and filter using vba. I don't, however, want it to affect the worksheet. I'd like to essentially copy the range into some native class that supports filtering and sorting (so i don't have to reinvent the wheel) and use that class to return a result to calling code.

Are there any classes I can use to this end? ListObject looked the most promising but it appears to require being tied to a worksheet range to work properly.

Answer 1

Why not copy the data to a new, hidden worksheet, perform your sort/filter there, and then copy the data back when done?

Answer 2

Hi I see you're still having issues with this. What is your user interface?

Answer 3

You can use recordsets. Here are some notes:

'Reference: Microsost ActiveX n.n Object Library '

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

'From: http://support.microsoft.com/kb/246335 '

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

'Must have client-side cursor for sorting '
rs.CursorLocation = adUseClient

'Substitute a name range for [Sheet1$] '
'or include a range of cells : [Sheet1&A1:C7] '

strSQL = "SELECT * FROM [Sheet1$] " _
       & "WHERE TransID>2 ORDER BY MyTime"

rs.Open strSQL, cn, 3, 3

rs.Filter = "TransID=3"
rs.Sort = "Mytime"

'Write out to another sheet '
Worksheets(2).Cells(2, 1).CopyFromRecordset rs

You may find this thread interesting: http://stackoverflow.com/questions/226978/syncing-two-lists-with-vba

Answer 4

If you'd like to read and parse complex sets of data you can use the Microsoft ActiveX Data Objects Recordset 2.8 Library. With this you can read your data into a recordset, then filter, sort, append, delete and pass it to other functions.

I regularly use this, because i often have to manipulate and display large datasets. If it's in a recordset i can use the same manipulation and presentation routines over and over again.

See Merge Excel Sheets.... for an example of throwing data into a recordset. After you have the data in a recordset then use r.filter = "ColumnA = 1", or r.sort = "ColumnC, ColumnA".

Answer 5

Turns out I can create a recordSet to do this. Unlike, Remou's answer though we don't have to invoke a heavy weight odbc process on our sheet.

The following function (adapted from Mark Nold's answer) will create a record set from the supplied range. It assumes column headers are in the first row of the supplied range. This can be made more robust but its a good starting spot

Function CreateRecordSet(rSource As range) As Recordset
    ' Constants
    Const MAX_CHARS = 1200

    ' Declarations
    Dim rs As Recordset
    Dim c As Long
    Dim r As Long
    Dim colCount As Long
    Dim rowCount As Long
    Dim fldName As String

    colCount = rSource.Columns.Count
    rowCount = rSource.rows.Count

    ' Setup record set
    Set rs = New Recordset
    r = 1 ' assume first row contains headers
    For c = 1 To colCount
        fldName = rSource.Cells(r, c).Value
        rs.Fields.Append fldName, adVarChar, MAX_CHARS
    Next c

    ' Fill record set
    rs.Open
    r = 2 ' skip header row
    For r = 2 To rowCount
        rs.AddNew
        Debug.Print "row "; r & " of " & rowCount & " created"
        For c = 1 To colCount
            rs.Fields(c - 1) = CStr(rSource.Cells(r, c).Value)
            Debug.Print "-- row(" & r; "): added col " & c & " of " & colCount
        Next c
    Next r

    Set CreateRecordSet = rs
End Function
Sub TestCreateRecordSet()
    Dim r As range
    Dim rs As Recordset

    Set r = range("A1:B4")
    Set rs = CreateRecordSet(r)
End Sub