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.
--
JJFJR

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 wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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


"Debra Dalgleish" wrote:

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 wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

FYI;

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


"Debra Dalgleish" wrote:

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 wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.
--
JJFJR


"jjfjr" wrote:

FYI;

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


"Debra Dalgleish" wrote:

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 wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

jjfjr wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

--
JJFJR


"Debra Dalgleish" wrote:

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

jjfjr wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html