macro to filter out data

[shabb090177]

I have an xls in which I need to FILTER OUT data

worksheet name: Format
columns name: a) Incentive Name
b) Level
criteria: a) Incentive Name = 'S' or 'N'
b) Level > '10'

I have very little experience in Excel previously, more so in macro/VBA
programming. Thanks in advance.

 

[Guest]

Hi,

click on the menu DATA | AUTO FILTER

Excel will create an arrow on the top of you column for the column A click
on the arrow select custom put igual S or N (you have two options) click OK

click on the column B arrow select Custom Higher than 10

HTH
Regards from Brazil
Marcelo

"shabb090177" escreveu:

 

[shabb090177]

Hi Marcelo. Thnx in advance. I need to have this in macro. Any sample
of codes?

 

[Guest]

Sub Filter()


ActiveWindow.ScrollRow = 1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=S*", Operator:=xlOr, _
Criteria2:="=N*"
Selection.AutoFilter Field:=2, Criteria1:=">10"

End Sub

 

[shabb090177]

Thanks Jon,

But this allows me to select only data that met the criteria. How do I
inverse/reverse the selection. I need to filter out these data.

My requirement, get data in Field A which is not S or N; and above 10
in Field B

(Field A != 'S' or Field A != 'N') and Field B >= 10

 

[Guest]

Sorry Shabb,

I must have misread the original criteria. This should do the trick. The
criteria are between the quotes. You can adjust them as appropriate.


Sub Filter()

ActiveWindow.ScrollRow = 1
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>S*", Operator:=xlAnd, _
Criteria2:="<>N*"
Selection.AutoFilter Field:=2, Criteria1:=">=10"

End Sub

 

[broro183]

hi Shabb,

"I have very little experience in Excel previously, more so in
macro/VBA
programming."

The best way to get an understanding of how VBA can be used in excel is
to record a macro as you complete the required action manually & then
modify the resulting code. To record a macro Tools - Macro - Record new
macro ie [alt + t + m + r], follow the prompts, complete your actions,
and stop recording (either the stop button on popup toolbar or [alt + t
+ m + r] again).

fyi, this would record almost an identical macro to the solution given
below.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[shabb090177]

Hi Rob,

Thing is I don't quite understand how the filter works in the first
place. Tried to understand and fit it into my requirement yet could not
see it.

Jon,

Thanks a lot. I still got to twitch it to suit what I am required to
produce but this is beautiful. Thank you again.

 

[Guest]

Glad to help. Let us know if there's something you can't figure out

JonR