RSS

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:

  1. 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.
  2. The input table is defined as a range.
  3. The table (range) values are copied to an array in one swift operation. The reason for using arrays is simple: speed!
  4. The macro loops through the array and copies rows that meet the criteria to another array.
  5. 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 (09)
  • [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           'Counter
Dim lCol As Long           'Counter
Dim lCount As Long         'Counter
Dim rInputTable As Range   'The range being searched
Dim rTarget As Range       'The output table
Dim arInput()              'Array with input table
Dim arOutput()             'Array for output table
Dim vPattern As Variant    'Search criteria/pattern

On Error GoTo ErrorHandle

'Query the user for an exact identifier or a pattern
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 the user clicked cancel or didn't write anything we exit
If Len(vPattern) = 0 Then Exit Sub

'You can define the input table as you like.
'In this case we use A1's current region.
'The "current region" is a range bounded by
'any combination of blank rows and blank columns.
'So if there are no blank columns or rows in the
'table, it is a convenient way to define the range.
Set rInputTable = Range("A1").CurrentRegion

'Copy the table to the array arInput.
'The array will automatically get the
'same dimensions as the table.
'The reason for using an array is speed.
arInput = rInputTable.Value

'The table has been copied to the array, arInput,
'so we don't need the range anymore and set it to
'nothing to save memory.
Set rInputTable = Nothing

'Redimensions the output array to the same dimensions
'(rows and columns)as the input table. This will nearly
'always be bigger than necessary, but unless you have
'limited space for the output table, it doesn't matter,
'because we fill it from the top, and the last records
'will be empty rows.
'1 To UBound(arInput) is the number of rows, and
'1 To UBound(arInput, 2) is the number of columns
ReDim arOutput(1 To UBound(arInput), 1 To UBound(arInput, 2))

'Loop through the input array and copy records that
'match the search pattern to the output array.
For lRow = 1 To UBound(arInput)
   'To compare we use the "Like" operator
   'instead of "=". This allows the use of
   'patterns/wildcards.
   'The code assumes that the identifier
   'is in the first column, but that can
   'easily be changed. If you do so, you
   'must be sure that the column exists,
   'or you will get an error!
   If arInput(lRow, 1) Like vPattern Then
      'If there is a match, we increment
      'the counter lCount and copy the
      'row to the output array.
      lCount = lCount + 1
      
      'All cell values to the right of the
      'first cell are copied. If you don't
      'want all cell values copied, you must
      'write a column number instead of
      'UBound(arInput, 2)
      For lCol = 1 To UBound(arInput, 2)
         arOutput(lCount, lCol) = arInput(lRow, lCol)
      Next
   End If
Next

'If lCount is zero, there was no match
If lCount = 0 Then
   MsgBox "No records matched your search criteria"
   GoTo BeforeExit
End If

'In this example we add a new workbook and copy the
'output array to range A1 etc.
'Of course you can define another destination, e.g.
'a specific workbook or just another sheet.
Workbooks.Add

'Dimension the target table to the same size as the
'output array.
Set rTarget = Range("A1").Resize(UBound(arOutput), UBound(arOutput, 2))

'Copy the array to the range in one swift operation.
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: