Seeking info on filtering

[paul.domaskis]

I've roamed the web for two afternoons looking for info on Excel
2003's autofiltering. Pages that might spell out the boundaries of
the filtering functionality. For example:

* That the filtering range is bounded at the bottom by a blank line
* That the down-arrows appear on the cells in the selected row when
filtering is turned on
* A clearer description of how to filter for different values across
the different columns. I've tried following the illustrated examples
on the web, MS site, and helpfiles...I'm obviously not doing it right.

Thanks for any referrals to online resources I might have missed.

 

[Sonbelt]

Hi Paul;

Here are a few code snippets that might help

'Turn off the auto filtering for the active sheet
ActiveSheet.AutoFilterMode = False

'Turn on the autofilter by default this will take row one
Selection.AutoFilter

Filter for a variable in columns 1, 2 & 3
Selection.AutoFilter Field:=1, Criteria1:=REGIONCELL
Selection.AutoFilter Field:=2, Criteria1:=TARGETCELL
Selection.AutoFilter Field:=3, Criteria1:=TARGETCELL

You did not ask for this but this is usually the next need. This is the
beginning of a code snippet that tries to validate that the filtering found
something.

Cells(1, 1).Select

With Worksheets(ActiveSheet.Name).Cells
Set c = .Find(TARGETCELL, LookIn:=xlValues,
MatchCase:=False, LookAt:=xlPart)
If Not c Is Nothing Then
'YOUR CODE GOES HERE
End If
End With


Thanks,


Greg

 

[Gord Dibben]

Yes, the filter lower boundary stops at a blank row.

Yes, the arrows appear in the row you selected................if you select
an entire row.

Autofilter will not filter across columns.

What effect do you want but don't seem to get?


Gord Dibben MS Excel MVP

 

[Alan]

Have a look here,

http://www.contextures.com/xlautofilter01.html

Regards,
Alan.

 

[paul.domaskis]

Thanks, Sonbelt. I figured out the ropes of filtering from the Data
pulldown menu. Managed to avoid code.

 

[paul.domaskis]

Yes, the filter lower boundary stops at a blank row.

Yes, the arrows appear in the row you selected................if you select
an entire row.

Autofilter will not filter across columns.

What effect do you want but don't seem to get?

I didn't figure out the Advance Filter, specifically that headers had
to be duplicated for the criteria range, and the cell referenced in
the criteria expression must be from the first data row (not header
row). of the table to be filtered. Got it figured out now. Thanks.

 

[paul.domaskis]

Have a look here,

http://www.contextures.com/xlautofilter01.html

Regards,
Alan.

Alan: Thanks for that. I did indeed find that page, but didn't read
it carefully enough.