How to play with filters of Excel headers

Help
1

Hello everybody,

After several unsuccessful searches on the forum and on the academy, I write you my very first question.

I have an excel board that I open with the scope. This table contains headers with filters.

My goal is to ask my robot to open the filters for column P in order to choose filters (“0”, “-”, “”). I can not validate this step.

I use the “Select Range” activity in my scope to position myself on the P column. This works

I use the activity “Send hotkey” with hotkey → alt + down to launch the small filter window. This does not work. It does not recognize my keyboard command or I’m wrong.

I tested by the activity “Select Item” but I have a crash of excel.

I may not be the right method.

My ultimate goal is to make my project flexible by sending other filter choices via my robot

Can you please help me?

thank you very much

Ps: I hope I’m not mistaken, if that’s the case I apologize

2

Do you have the Special property checked for the Send Hotkey?
Also, make sure you are selecting the first cell of the column or you can’t access the filter options. You can do Ctrl+f to search for a header name, which is what I usually do.

I am accessing the Filter options with TypeInto

TypeInto "[d(alt)][k(down)][u(alt)]"

Then you can include other keystrokes to change the options, which are underlined:

TypeInto "[d(alt)][k(down)][u(alt)]fe[k(enter)]"

Hopefull that points you in the right direction.

Regards.

3

Thank you very much ClaytonM for your quick response.
I’m testing this tomorrow morning.

I’ll get back to you as soon as possible

1 Like
4

Hello ClaytonM,

Thank you for this solution that works by using the “Simulate click” option. Without this option, Excel crash.

I tested the second “Type Into”.

It does exactly what I want. It deselects all the list proposed by the filter. It is very good.

TypeInto “[d (alt)] [k (down)] [u (alt)] fe [k (enter)]”

But to become autonomous, I need to understand this Type Into.
For the “[d (alt)] [k (down)]” => it seems obvious that it simulates alt + down arrow.

But I can not translate what [u (alt)] fe [k (enter)] does.
Why the “u”?
and Why the “fe”?

Thank you if possible for giving me details on these shortcuts.

Thank you for accepting my apologies for my level of English.
I will look if I find information.

Now that my filter is open I have deselected my list. I need to select from this list those that are “0”, those that have a “-” and those that are empty.

I imagine that we can pass these choices by our “Type Into”.

Anyway thank you very much for your reactivity

5

My excel file crash often. I added at the end of my scenario a “message box” to prevent it from crashing. But this is not the subject.

Here is my approach for the moment.

here I affect my range for filter processing
Show%20Filter%20List

here I run my window displaying the list of choices to filter
Assign%20filters

here I deselect the complete list and then choose the following criteria “0”, “-”, “empty”

UnselectAll
UnselectAll359×557 9.47 KB

I am now choosing “0” in this list of choices but I would also like to choose “-” and “empty” or “”.

select%200
select%200343×525 8.42 KB

It’s good if I only have the choice “0”. I chose the smallest to the largest, so I would have no problem finding this “0” in this list because it will always be available at the top of the list.

This gets complicated if I need to choose the “empty cells” as well as the cells with a dash.

For empty cells, I can not use [k (down) since my list is dynamic. I could have a smaller or bigger list. In this case, what solution is possible?

Thanks for your help

6

Hello.
“d” for down, “u” for up, and “k” for keypress… if you do a down press, you must follow with an up press or the key will be struck down.

When you show the Filter options after Alt+down, you will notice that each line has a letter underlined. If you press that letter, it will select the option. “f” was selecting the “Number Filter” line since “F” was underlined. “e” was selecting the next option for “Equals” since the “E” was underlined.

So, you can choose any option you would like just by typing in each letter that is underlined.

You will also see that “Clear Filters” has the “C” underlined so you can clear all filters that way.

The only thing I am not sure about is how to do more than 2 filters on a column, because a Custom Filter only has 2 fields. You might need to do some Excel research on that.

It’s better to avoid using Down keystrokes to select the check boxes and use Equals, Contains, or other Custom Filters… in my opinion.

You might also consider Datatable manipulation cause it’s much more powerful mainly with filtering down to only certain rows. To be honest, I would only use the Excel filters to set up a file the way you want for the end customer. But it depends on what you are trying to do also. Datatables also require a little more learning curve to climb cause it’s like .net coding (it’s not too difficult though).

Regards.

7

Thanks ClaytonM. your answer is complete