Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a sheet with 69 columns and 6600 rows called Raw Data. I also have a sheet called Filtered Data. I have a drop down menu on the Filtered Data sheet in cell B4. The list in the drop down menu corresponds to the columns of data in the Raw Data sheet. I use cell B5 to enter a min value and cell B6 to enter a max value. I want to filter the Raw Data sheet according to the column selected via the drop down menu such that the values in that column are greater than or equal to my min value and less than or equal to my max value.

The code does not filter.

Private Sub ExtractData(ByVal Filter As Range)
'Dim variables
Dim LR As Long, NR As Long
Dim filterItem As String
Dim minValue As Variant, maxValue As Variant
Dim colNum As Integer
Dim rng As Range, min As Range, max As Range
Dim shSource As Worksheet
Dim shDest As Worksheet

'Set range and source and target worksheets
Set shSource = ThisWorkbook.Sheets("Raw Data")
Set shDest = ThisWorkbook.Sheets("Filtered Data")

'shSource.Range("D11:BP11") is the list of all possible drop down menu items
Set rng = shSource.Range("D11:BP11")

'Set min and max filter cells
Set min = shDest.Range("B5")
Set max = shDest.Range("B6")

'Define min and max filter values
minValue = shDest.Range("B5").Value
maxValue = shDest.Range("B6").Value

filterItem = Filter.Value
'Determine which column contains the filter category
colNum = Application.Match(filterItem, rng, 0)

If Not IsError(colNum) Then
    Select Case colNum
        Case 1 To 3:  'Columns B to F
            min.NumberFormat = "@"  'string format
            max.NumberFormat = "@"
        Case 4 To 11, 14, 22 To 23, 29, 33 To 37, 46 To 47, 57, 60 To 61, 63, 65:
            min.NumberFormat = "0.00"  'number format
            max.NumberFormat = "0.00"
        Case Else:
            min.NumberFormat = "0.00%"  'percentage format
            max.NumberFormat = "0.00%"
    End Select

    NR = shDest.Range("A" & rows.Count).End(xlUp).Offset(1).Row 'Go to cell below last used cell in column A

    With shSource
        LR = .Cells(rows.Count, "A").End(xlUp).Row  'Last row of column A
        .AutoFilterMode = False
        With .Range("A12" & LR)
            .AutoFilter Field:=colNum, Criteria1:=">=" & minValue, Operator:=xlAnd, Criteria2:="<=" & maxValue, VisibleDropDown:=False
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy shDest.Range("A" & NR)
            .AutoFilter
        End With
    End With
Else
    MsgBox filterItem + " filter criterion was not found."
End If

shDest.Activate
End Sub
share|improve this question
    
With .Range("A12" & LR) <--- this is probably not giving the right cell to actually autofilter. It's going to be something like A121 or higher which is probably not the range you want to autofilter. –  enderland May 3 '13 at 19:32
    
I am not sure how to change this. I want ideally to start with cell A12 and go across all columns all the way down to the last row. Not sure how to specify that properly. –  PBrenek May 3 '13 at 19:35
    
Got an error stating that no cells were found. I used the Range("A12:BQ12").Autofilter –  PBrenek May 3 '13 at 19:49
    
sthg you can try, if you set-up your list as a table (Home-->format as a table), you can then adress it in your code by the name of the table, for example using: Application.Goto Reference:="Table2" it'll select the table, you can then do your selection.autofilter etc. –  Jo Bedard May 3 '13 at 19:54
    
also,as @enderland noticed, Range("A12" & LR) is the equivalent of Range("A12a121"). It would not work, it has to be Range("A12:"& LR), with a ":". –  Jo Bedard May 3 '13 at 20:01

2 Answers 2

I was misunderstanding you.

You want to use

Dim str As String
str = Range("a12").CurrentRegion.Address
Range(str).AutoFilter
'or
Range("A12").CurrentRegion.AutoFilter

this will filter the entire region that those cells are in.

Alternatively, you could use something like (if there are missing cells or holes this might be useful).

Dim str As String
str = "a12:BQ" & shDest.Range("A" & rows.Count).End(xlUp).Offset(1).Row
Range(str).AutoFilter
share|improve this answer
    
I am sorry but I am not sure how I would use that in the context of what I am trying to do. I do have some blank cells in the data but I am thinking this should not be an issue with the autofilter. –  PBrenek May 3 '13 at 20:14

I finally got the autofilter working. The following is what I used:

    With shSource
        LR = .Cells(rows.Count, "B").End(xlUp).Row 'Last row of column B
        .AutoFilterMode = False
        With .Range("B11:BQ" & LR)
            .AutoFilter Field:=colNum, Criteria1:=">=" & minValue, Operator:=xlAnd, Criteria2:="<=" & maxValue, VisibleDropDown:=False
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy shDest.Range("A" & NR)
            .AutoFilter
        End With
    End With

Not perfect as it does not copy over the header but it works. The other issue is that the criteria do not seem to be working.. Will work on that.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.