Find and copy rows using criteria
A frequently asked question is how to copy rows that meet certain criteria from one table to another - maybe in another workbook or worksheet.
The condition for copying can be a specific customer ID, a serial number, a name, a pattern with wildcards or whatever.
This page shows an example on how to do that with a fast Excel VBA macro.
It assumes that the identifier (e.g. customer ID) is in the first column of the table, but that can easily be changed.
The macro has a few steps:
- The user must write the identifier (or pattern) in an inputbox. If you always use the same identifier/condition skip this and declare it as a constant instead.
- The input table is defined as a range.
- The table (range) values are copied to an array in one swift operation. The reason for using arrays is simple: speed!
- The macro loops through the array and copies rows that meet the criteria to another array.
- When done it opens a new workbook and inserts the copied rows as a new table starting in cell A1. This can easily be changed to e.g. another worksheet or a specific workbook.
The macro does not copy the entire row from the original worksheet - it will only copy the table's rows/columns.
The macro is fast because it uses arrays and only operates on a worksheet two times: When the table is defined as a range and copied to the array, and when the output array is inserted as a new table.
Looping the range and copying the rows "conventionally" would be much slower, because there is a lot of overhead, when you operate directly on a worksheet.
The Like operator
The macro uses the "Like" operator to find the rows that must be copied. This can find exact matches, but it can also find patterns by using wildcards.
For instance "2*e" will find all values that start with "2" and ends with "e". The possible wildcards are:
- ? Any single character
- * Zero or more characters
- # Any single digit (0–9)
- [charlist] Any single character in charlist
- [!charlist] Any single character not in charlist
By default VBA uses binary comparison and will distinguish between upper and lower case. If you want A=a, B=b etc. you must declare: "Option Compare Text" at the top of your VBA module.
For more information about the Like operator see the VBA help.
The macro assumes that the table being searched starts i cell A1 on the active sheet, and defines the range using "CurrentRegion". If you have blank rows or columns in your table, you must define the range in another way.
Here we go. To try the macro, highlight the code, copy (CTRL+C) and insert (CTRL+V) into a VBA module.
Sub CopyRows()
Dim lRow As Long
Dim lCol As Long
Dim lCount As Long
Dim rInputTable As Range
Dim rTarget As Range
Dim arInput()
Dim arOutput()
Dim vPattern As Variant
On Error GoTo ErrorHandle
vPattern = InputBox("Write identifier for records to be copied" & vbNewLine _
& "You can use wildcards to make patterns:" & vbNewLine & vbNewLine _
& "? Any single character" & vbNewLine _
& "* Zero or more characters" & vbNewLine _
& "# Any single digit (0-9)" & vbNewLine _
& "[charlist] Any single character in charlist" & vbNewLine _
& "[!charlist] Any single character not in charlist", "Identifier")
If Len(vPattern) = 0 Then Exit Sub
Set rInputTable = Range("A1").CurrentRegion
arInput = rInputTable.Value
Set rInputTable = Nothing
ReDim arOutput(1 To UBound(arInput), 1 To UBound(arInput, 2))
For lRow = 1 To UBound(arInput)
If arInput(lRow, 1) Like vPattern Then
lCount = lCount + 1
For lCol = 1 To UBound(arInput, 2)
arOutput(lCount, lCol) = arInput(lRow, lCol)
Next
End If
Next
If lCount = 0 Then
MsgBox "No records matched your search criteria"
GoTo BeforeExit
End If
Workbooks.Add
Set rTarget = Range("A1").Resize(UBound(arOutput), UBound(arOutput, 2))
rTarget.Value = arOutput
BeforeExit:
On Error Resume Next
Set rTarget = Nothing
Erase arInput
Erase arOutput
Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure CopyRows"
Resume BeforeExit
End Sub
That was it. You can use similar techniques to delete rows or merge data. The most important point is that you get much faster code if you operate on arrays and limit direct operation (reading and writing) on the worksheet to a minimum.
Below are links to related pages.
Related:
|