Macro isn't keystroke recording, I need to show only rows that are not zero in one particular column

[jamesinnewcastle]

Hi All

My first question. I use Excel, have programmed in Basic, have coded in machine code. I can sort of work out what is going on in a program but I don't know any of the instruction set of VBA.

I have an Excel sheet that lists various products in one column. The user types in the number of each item he wants in the next column. I then use the 'Filter' button (Autofilter?) on the 'number' column and by unticking 0 the filter removes all the products not selected. So starting with

Beans 0
Hats 1
Nails 0
Bats 55
eggs 0

Filter on column 2 and untick 0 and it produces:

Hats 1
Bats 55

Great!

However the end user is not Excel savvy and will forget how to filter the results so I have added a 'button' to do all the column section, filtering, and deleting the 0, etc. This means all he has to do is push the button to get the list of items he selected.

So with a keystroke recorder this should be easy, I eagerly recorded the keystrokes as a macro only to find that it doesn't reproduce the keystrokes but rather rebuilds the Filter "pull down" of the original sheet used to make the 'recording' and so when I change the number of products or add products the Macro just tries to reapply the selection of the original sheet and any new items added since making the macro do not show up.

I have fiddled with the instructions in the Macro using examples from the internet, and while I can produce working Macros by doing this, they just don't do what I need.

I have looked at third party keystroke recorders but they don't allow me to put a big button on the Excel sheet and my users certainly would not want to add the keystroke program to their computers!

I know that I should look deeper into VBA and do this for myself but it seems like such a simple task that you guys might be able to just reel off a solution or suggest an alternative, any suggestions would be appreciated!

Cheers
James

 

[Fluff]

Hi & welcome to MrExcel.
Try

   Range("A1:B1").AutoFilter 2, "<>0"

 

[jamesinnewcastle]

Hi & welcome to MrExcel.
Try

   Range("A1:B1").AutoFilter 2, "<>0"

Hi Fluff

That works OK thank you! I'm now trying to fiddle it into my real sheet that naturally was a lot more complex than I stated! I'll go on by myself for a while until I get stuck again!

Can I assume that you are using short cuts so that .AutoFilter 2 is more formally .AutoFilter Field:=2?

 

[Fluff]

You don't need to use the name of the argument, but all arguments must be in the correct order.
So you could use

Range("A1:B1").AutoFilter 2, "<>0", xlAnd, "<5000"

Which will filter the 2nd column if it's not 0 & is less then 5000, or you could write it like

Range("A1:B1").AutoFilter field:=2, Criteria1:="<>0", Criteria2:="<5000", Operator:=xlAnd

which does the same thing, but as the argument names are used, you can put them in any order you like

 

[jamesinnewcastle]

So you could use

Range("A1:B1").AutoFilter 2, "<>0", xlAnd, "<5000"

[/QUOTE]

Hi Fluff

Thank you for that - as it happens I have to ignore 0 and Null as <>0 is specific and most often they won't put any number in. So I will try:

Range("A1:B1").AutoFilter 2, "<>0", xlOr, "NULL" 

If NULL doesn't work I'll try ""

I'll have a play!

Cheers
James