Filtering Data
 

I would like to filter (not autofilter, the other one, is "Special
Filter" the exact name in english?) data in my excel Sheet, but it
just does not work...

Here my Problem:
I have a list with several columns, most of them include text. Then I
want filtering data, so that it only shows me the rows with the
following condition in a specific field:
[Content] < ''
[Content] < 'xyz'
-- the field must not be empty and must not contain the word 'xyz'...

How can I realize that?

A second problem is, that the output is static, means that it doesn't
change when the source changes. I think, I can solve this with vba.
Can somebody say the corresponding function?

Dou you know a good website or document with such information, i
rarely found some good stuff for vba/excel.

Ok... many questions, I know... Thanks for your trouble in advance.

Michael

Filtering Data
 

Hi Michael

sorry, not really sure what you're after, but check out
www.contextures.com/tiptech.html for lots of good info on filtering (and
other things), if you can't find your answer there, please post back and
type out a small sample of your existing data and what you want to be able
to achieve usingthe filter.
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Michael" wrote in message
om...
I would like to filter (not autofilter, the other one, is "Special
Filter" the exact name in english?) data in my excel Sheet, but it
just does not work...

Here my Problem:
I have a list with several columns, most of them include text. Then I
want filtering data, so that it only shows me the rows with the
following condition in a specific field:
[Content] < ''
[Content] < 'xyz'
-- the field must not be empty and must not contain the word 'xyz'...

How can I realize that?

A second problem is, that the output is static, means that it doesn't
change when the source changes. I think, I can solve this with vba.
Can somebody say the corresponding function?

Dou you know a good website or document with such information, i
rarely found some good stuff for vba/excel.

Ok... many questions, I know... Thanks for your trouble in advance.

Michael

Filtering Data
 

First problem.

Sub FilterData()

With Columns("J:J")
.AutoFilter Field:=1, _
Criteria1:="<", _
Operator:=xlAnd, _
Criteria2:="<xyz"
End With

End Sub

Second problem.

Assign the macro to a button and click on demand.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Michael" wrote in message
om...
I would like to filter (not autofilter, the other one, is "Special
Filter" the exact name in english?) data in my excel Sheet, but it
just does not work...

Here my Problem:
I have a list with several columns, most of them include text. Then I
want filtering data, so that it only shows me the rows with the
following condition in a specific field:
[Content] < ''
[Content] < 'xyz'
-- the field must not be empty and must not contain the word 'xyz'...

How can I realize that?

A second problem is, that the output is static, means that it doesn't
change when the source changes. I think, I can solve this with vba.
Can somebody say the corresponding function?

Dou you know a good website or document with such information, i
rarely found some good stuff for vba/excel.

Ok... many questions, I know... Thanks for your trouble in advance.

Michael

Filtering Data
 

Thank's a lot, on this site I found what I needed!

Now to the second Problem... The solution posted from Bob Phillips is
for AutoFiltering, but i would like to use the advanced filters. Can't
they be controlled by vba?

Filtering Data
 

Why, doesn't that work?

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Thank's a lot, on this site I found what I needed!

Now to the second Problem... The solution posted from Bob Phillips is
for AutoFiltering, but i would like to use the advanced filters. Can't
they be controlled by vba?

Filtering Data
 

It works, but not just in that way I wanted to. I wanted to use
advanced filtering, so that I can show the filtered data in an other
sheet and not with all the columns of the source data.

But it looks as I have to solve it with AutoFiltering. Then I can hide
the unused columns... It's ok too...

Filtering Data
 

Thanks a lot for your help!

Filtering Data
 

or alternatively, copy the results of the autofiltering to another sheet ...

e.g. to add to Bob's macro

Sub FilterData()

With Columns("J:J")
.AutoFilter Field:=1, _
Criteria1:="755", _
Operator:=xlAnd, _
Criteria2:="<1000"
End With
Range("A1").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy Sheets("Sheet1").Range("A1") 'change name as applicable
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select

End Sub

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
wrote in message
oups.com...
It works, but not just in that way I wanted to. I wanted to use
advanced filtering, so that I can show the filtered data in an other
sheet and not with all the columns of the source data.

But it looks as I have to solve it with AutoFiltering. Then I can hide
the unused columns... It's ok too...

Filtering Data
 

you can use advanced filter in VBA.

expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

Set it up just like you would to use advanced filter manually. (in fact,
you might do it manually with the macro recorder turned on to get the code)
--
Regards,
Tom Ogilvy


wrote in message
oups.com...
It works, but not just in that way I wanted to. I wanted to use
advanced filtering, so that I can show the filtered data in an other
sheet and not with all the columns of the source data.

But it looks as I have to solve it with AutoFiltering. Then I can hide
the unused columns... It's ok too...

Filtering Data
 

Julie's addition is proabbly best as the advanced filter does not seem to
copy to another sheet, although I would avoid selecting (yuk!)

Sub FilterData()
Dim rngTarget As Range

Set rngTarget = Worksheets("Sheet1").Range("A1") 'change as applicable

With Columns("J:J")
.AutoFilter Field:=1, _
Criteria1:="755", _
Operator:=xlAnd, _
Criteria2:="<1000"
.SpecialCells(xlCellTypeVisible).Copy rngTarget
Application.CutCopyMode = False
.AutoFilter
End With

rngTarget.Parent.Activate
rngTarget.Select
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JulieD" wrote in message
...
or alternatively, copy the results of the autofiltering to another sheet
....

e.g. to add to Bob's macro

Sub FilterData()

With Columns("J:J")
.AutoFilter Field:=1, _
Criteria1:="755", _
Operator:=xlAnd, _
Criteria2:="<1000"
End With
Range("A1").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy Sheets("Sheet1").Range("A1") 'change name as applicable
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select

End Sub

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
wrote in message
oups.com...
It works, but not just in that way I wanted to. I wanted to use
advanced filtering, so that I can show the filtered data in an other
sheet and not with all the columns of the source data.

But it looks as I have to solve it with AutoFiltering. Then I can hide
the unused columns... It's ok too...

Filtering Data
 

You can filter the results to a different sheet, if you start on the
destination sheet. There are instructions he

http://www.contextures.com/xladvfilter01.html#ExtractWs

Bob Phillips wrote:
Julie's addition is proabbly best as the advanced filter does not seem to
copy to another sheet, although I would avoid selecting (yuk!)


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html