How can I filter and copy data in Excel?

Question

Hi,

I have a lot of data in an excel worksheet. For calculations, I would like to restrict this data to the relevant data only. That is: filter the data and put the subset in another worksheet.
Relevant data is data that falls within a given minimum and maximum value.

For example:
Suppose I want to filter column A for values between 1 and 2, and column B for values between 0 and 1. Result should become like this.

  A B C = Data
1 0 0 0
2 1 1 0
3 2 0 3
4 2 2 1

  A B C = Result
1 1 1 0
2 2 0 3

Is there an easy solution for this?
The fact that I don't filter on exact matches apparently makes the problem more difficult.

Thanks in advance!

Answer 1

Hi neXus,

I've got a quick VBA procedure that will do just what you want...

Private Sub MultiFilter(DataRange As Range, CriteriaRange As Range, OutputRangeTL As Range)
    Dim intRowCounter As Integer
    Dim intColCounter As Integer
    Dim varCurrentValue As Variant
    Dim blnCriteriaError As Boolean
    Dim rngOutputCurrent As Range

    If CriteriaRange.Columns.Count <> DataRange.Columns.Count Then
        Err.Raise Number:=513, Description:="CriteriaRange and DataRange must have same column count"
    End If
    If CriteriaRange.Rows.Count <> 2 Then
        Err.Raise Number:=513, Description:="CriteriaRange must be of 2 rows"
    End If

    Set rngOutputCurrent = OutputRangeTL.Resize(1, DataRange.Columns.Count)

    For intRowCounter = 1 To DataRange.Rows.Count
        For intColCounter = 1 To DataRange.Columns.Count
            varCurrentValue = DataRange.Cells(intRowCounter, intColCounter).Value
            If Not (varCurrentValue >= CriteriaRange.Cells(1, intColCounter) _
            And varCurrentValue <= CriteriaRange.Cells(2, intColCounter)) Then
                ''#i.e. criteria doesn't match
                blnCriteriaError = True
                Exit For
            End If
        Next intColCounter
        If Not blnCriteriaError Then
            ''#i.e. matched all criteria
            rngOutputCurrent.Value = DataRange.Resize(1).Offset(intRowCounter - 1).Value
            Set rngOutputCurrent = rngOutputCurrent.Offset(1)
        End If
        blnCriteriaError = False
    Next intRowCounter
End Sub

Usage:

DataRange:
0 0 0
1 1 0
2 0 3
2 2 1

CriteriaRange:
1 0 0
2 1 10

Then do:

Public Sub DoTheFilter()
    MultiFilter Range("MyDataRange"), Range("MyCriteriaRange"), Range("MyOutputRangeTopLeft")
End Sub

The CriteriaRange is simply a 2 row range giving minimum and maximum values for each column.

This isn't the most elegant of most efficient way I'm sure, but I used it as a quick fix as I've needed to do this once or twice.

If you're not comfortable with using VBA code then let me know and I'm sure I can manage to convert it into a worksheet function for you (this would also have the added advantage of updating if you changed the criteria...)

Simon