Excel, VBA and my achillies heel

[RicksAtWork]

I have selected a range in VBA.

Is it possible to filter this range?

For example, if the range contained a column called Country, is it possible to select all rows with a country value of Japan?

 

[Loomah]

Hi
Have you tried using the macro recorder to record yourself doing this manually?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[RicksAtWork]

Will this generate the VBA?

I am a total newbie, and while code snippets are helpful there a lots of functionality that I am clearly unaware of!!

So I often do things the long way round!

 

[RicksAtWork]

I have a method that I want to be able to pass a string into i.e. "Japan". This I would like to filter my Range "Data" and return all the rows that have the word Japan in their country column.

As far as I can tell the AdvancedFilter method only allows you to filter a range, with another range...

Sub GetKOLsByCountry(country As String)


End Sub

Is this a stupid method?

 

[Loomah]

To answer your first question - Yes!
Go
TOOLS>MACRO>RECORD NEW MACRO

When you have accepted the message (changing name and location if appropriate) then select your range and go

DATA>FILTER>AUTO FILTER

You will get a series of buttons on the top row of your data. From the column headed Country click the button and choose Japan (or whatever) from the list.

Stop the recorder and see what code you are left with (ALT+F11 to activate the VBE).

If you want to be able to pass a variable into the filter code you could either use an Input Box or a User Form (see help for both).

Finally, there are no (really) stupid methods if it wirks. Some are just better than others!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[RicksAtWork]

I cant select 'Record New Macro'!!!!

 

[SkipVought]

Did you have macros turned off when you started Excel?




Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}