Advancedfilter returning all rows instead of ones that match crit

[jjfjr]

Hi;

I have a workbook with two sheets: Data and Results. My search button on
the Results sheet will see what criteria has been entered in the
criteria section of Results and then display rows in the Data sheet that
match the Criteria. The matching rows are displayed in the Result
section of the Result sheet. I have the following declarations:

Dim MaxResults As Integer, MyCol As Integer, ResultsRng As String
Dim MyRow As Integer, LastDataRow As Integer, DataRng As String
Dim CritRow As Integer, CritRng As String, RightCol As Integer
Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer

DataRng = "$A$2:$H$9" 'range of cells to searched on the data sheet
CritRng = "$B$3:$I$3" ' range of cells for Criteria table
ResultsRng = "$B$9:$I$1000" ' range of headers for Results table

Towards the end of the code is the following line:

Worksheets("Data").Range(DataRng).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range(CritRng), CopyToRange:=Range(ResultsRng), _
Unique:=True

Instead of returning only the rows in the "Data" sheet that match the
criteria range, all rows in "Data" are returned to the Results section of the
Results sheet. DataRng ,CritRng and ResultsRng all seem to have the proper
values. This occurs whether I set Unique in the AdvancedFilter statement to
true or false.

Any help is greatly appreciated.

 

[Debra Dalgleish]

Your criteria range needs to include the heading cells, and the criteria
cells. Your range is only one row:

CritRng = "$B$3:$I$3" ' range of cells for Criteria table

 

[jjfjr]

Hi;

I changed critRng to $B$3:$I$3 so that the header cells are included. I
tried a simple criteria with only one criteria cell with text to search on.
It seemed to work in that I now get only one row as a hit however, when I
clear out the criteria cells and type in some different info in another
cell(s) for another search, I keep getting the same row that was brought up
on my first search. Is there any way to clear out the advanced filter
mechanism? Could there be another problem? I also looked at the webpage URL
that you sent with your response. Any help is very appreciated.

 

[jjfjr]

FYI;

In my previous response I meant to say that I changed critrng to $B$2:$I$3
to include the header cells.

 

[jjfjr]

I've done some more inspection on the behavior of the macro and it seems to
be always returning the first row of the "Data" sheet no matter what criteria
I put in.

 

[Debra Dalgleish]

What's in the criteria area, and what is some sample data that you
expect would be returned for that criteria?

 

[jjfjr]

Hi;

Thanks for the response. My workbook captures information about aircraft
components. In the "Data" sheet the row headings are Location, Section,
Shelf, Manufacturer Number, Part Serial Number, Item Description, Comment
and Condition. The "Results" sheet has the Criteria in the top three rows and
the Results are displayed in the lower portion. When I enter "F6F" in the
Item Description column in the Criteria section, I would expect to get all
rows with F6F in that column. However, while the appropriate row was
returned, after I clear out the criteria and enter something else the same
row from the first search (it also happens to be the first row) appears. I
experimented by adding a new row at the top of the Data sheet and sure enough
when ever I enter any info into any column of the criteria range this top row
appears in the results area.

Ultimately, what I want to be able to do is put info in so that I can AND
and OR criteria as stated in the website URL you sent. If neeccessary, I can
email you the workbook for your inspection.

Thanks

 

[Debra Dalgleish]

You mention that criteria are in the top three rows, but your code only
mentions rows 2:3. Are the criteria range headings in cells B2:I2?

CritRng = "$B$2:$I$3" ' range of cells for Criteria table

Also, your data range is only 8 rows. Is that what you intended?

DataRng = "$A$2:$H$9" 'range of cells to searched on the data sheet

 

[jjfjr]

Hi;

The logic has changed a bit. The following is the bulk of the code:

Private Sub Search_Click()

Dim MaxResults As Integer, MyCol As Integer, ResultsRng As String
Dim MyRow As Integer, LastDataRow As Integer, DataRng As String
Dim CritRow As Integer, CritRng As String, RightCol As Integer
Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer

' the source data is in a worksheet called 'Data'

' *** MODIFY AND SET YOUR OWN RANGES ON THE FOLLOWING DECLARATIONS ***

' cell Data!E1 contains the last row number of data [=COUNT(E4:E100)+3]

MsgBox "Before declarations"
MsgBox LastDataRow
MsgBox Worksheets("Data").Range("E1").Value
LastDataRow = Worksheets("Data").Range("E1").Value
MsgBox LastDataRow

DataRng = "A3:H3" ' range of column headers for Data table
CritRng = "B3:I5" ' range of cells for Criteria table
ResultsRng = "B8:I8" ' range of headers for Results table
MaxResults = 1000 ' any value higher than the number of possible results
MsgBox "After declarations"
' **************** END OF DECLARATIONS *********************


' fix the data range to incorporate the last row
MsgBox "Before data range fix"
TopRow = Worksheets("Data").Range(DataRng).Row
MsgBox "TopRow= " & TopRow

LeftCol = Range(DataRng).Column
RightCol = LeftCol + Range(DataRng).Columns.Count - 1
DataRng = Range(Cells(TopRow, LeftCol), Cells(LastDataRow, RightCol)).Address
MsgBox "After data range fix"
' fix the results range to incorporate the last row

TopRow = Worksheets("Data").Range(ResultsRng).Row
MsgBox "TopRow= " & TopRow
LeftCol = Range(ResultsRng).Column
RightCol = LeftCol + Range(ResultsRng).Columns.Count - 1
ResultsRng = Range(Cells(TopRow + 1, LeftCol), Cells(MaxResults,
RightCol)).Address
Range(ResultsRng).ClearContents ' clear any previous results but not headers
ResultsRng = Range(Cells(TopRow + 1, LeftCol), Cells(MaxResults,
RightCol)).Address

' fix the criteria range and identify the last row containing any items

TopRow = Range(CritRng).Row
MsgBox "TopRow= " & TopRow
BottomRow = TopRow + Range(CritRng).Rows.Count - 1
MsgBox "BottomRow= " & BottomRow
LeftCol = Range(CritRng).Column
MsgBox "LeftCol= " & LeftCol
RightCol = LeftCol + Range(CritRng).Columns.Count - 1
MsgBox "RightCol= " & RightCol
CritRow = 0

For MyRow = TopRow To BottomRow
MsgBox "MyRow=" & MyRow
For MyCol = LeftCol To RightCol
MsgBox "MyCol=" & MyCol
MsgBox "Cells(MyRow,MyCol).Value=" & Cells(MyRow, MyCol).Value
If Cells(MyRow, MyCol).Value <> "" Then CritRow = MyRow
MsgBox "CritRow= " & CritRow
Next
Next

MsgBox " Before If CritRow= " & CritRow

If CritRow = 0 Then
MsgBox "No Criteria detected"
Else
CritRng = Range(Cells(TopRow - 1, LeftCol), Cells(CritRow, RightCol)).Address
MsgBox "DataRng= " & DataRng
MsgBox "CritRng= " & CritRng
MsgBox "ResultsRng= " & ResultsRng
MsgBox "Worksheets(Results).Range(CritRng)= " &
Worksheets("Results").Range(CritRng).Address

Worksheets("Data").Range(DataRng).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("Results").Range(CritRng),
CopyToRange:=Worksheets("Results").Range(ResultsRng), _
Unique:=True
End If
Range("A5").Select
End Sub

The MsgBox messages are there to echo back the various values that variables
currently hold. Any ideas?