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.
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: syncing two lists with VBA
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".
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
You want to use a Range class (just like CasperOne says). Here's some example VBA code
Function SortAndFilter(rSource As Range) As Range
Dim rResult As Range
Dim vaTemp As Variant
Dim wsTemp As Worksheet
Dim wbTemp As Workbook
vaTemp = rSource.Value
Set wbTemp = Workbooks.Add
Set wsTemp = wbTemp.Sheets(1)
Set rResult = wsTemp.Range("A1").Resize(UBound(vaTemp, 1), UBound(vaTemp, 2))
rResult.Value = vaTemp
rResult.Sort rResult.Cells(1), xlDescending
Set SortAndFilter = rResult
End Function
Sub Testit()
Dim rTest As Range
Set rTest = SortAndFilter(Selection)
'Do stuff with your range object
rTest.Parent.Parent.Close False 'close temp workbook
End Sub
Why not copy the data to a new, hidden worksheet, perform your sort/filter there, and then copy the data back when done?
