Macro help

[N. McCain]

I have a pricing template that allows the users to put in the quantity of
what equipment the are going to rent. When they are finished, the press CTRL
+ Z and it will filter out all of the rows where there was not a quantity
entered. Someone from the company created the macro 3 years ago, and now
they no longer are with us. The problem that I am having now is that we
needed to add some new equipment and now the new equipment will not filter.
When I put in 1 in for the quantity of any new equipment, press CTRL + Z, it
does not show up. Below is the code. I am hoping that someone can explain
why the new equipment does not show up, and also give me some suggestions on
what to do. Thanks in advance.

Sub Macro1()
Cells.Select
Selection.AutoFilter
ActiveWindow.SmallScroll Down:=0
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 102
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 89
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 76
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 69
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
Selection.AutoFilter Field:=25, Criteria1:=">=1", Operator:=xlAnd
ActiveWindow.SmallScroll Down:=-39
End Sub

 

[Bob Phillips]

It looks ok, but can be simplified to jus

Sub Macro1()
With Cells

.AutoFilter
.AutoFilter Field:=25, Criteria1:=">=1", Operator:=xlAnd
End With
End Sub

Long shot,. but does thios work?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[N. McCain]

It is still now showing the new rows that were inserted. Is there a way that
I can specify the range to select so that it will pick up on these new rows?

 

[Don Guillett]

The "macro" worked as it should but this would be better. Apparently you do
NOT have it assigned to the shortcut or you are using Z when it should be z
or vise versa.

Sub filtercol25greaterthanorequaltoone()
Cells.AutoFilter Field:=25, Criteria1:=">=1"
End Sub

Manually assign Tools>macro>macros>this workbook>find your macro>options>
or
Sub Addshortcutkey()
Application.MacroOptions Macro:="filtercol25greaterthanorequaltoone", _
Description:="filter col 25", ShortcutKey:="z"
End Sub

 

[N. McCain]

Well, it is still not reading the new rows that were added. For example: I
have some equipment in rows 21-38. The new equipment that I added is in rows
23 & 24. These two rows will not show up when I put a 1 in the quantity
column, but all the others will show up if I have put a 1 in the quantity
column.

 

[N. McCain]

Is there a reason that in this code: (.AutoFilter Field:=25,
Criteria1:=">=1", Operator:=xlAnd) : Field has to equal 25??

 

[Don Guillett]

Are you clearing the filter? Are you re-applying the filter after adding.
BTW ctrl Z is a poor choice as you no longer have an undo.

 

[N. McCain]

Don, I appriciate all your help. Here is what I have going on, and see if
you know of a better way to do it. Example below
Row 20: QTY | Nike Shoes | Sell Price
Rows 21 - 38: Qty is blank | under Nike Shoes I have different types.
Row 39: Same as Row 20 but it's Adidas Shoes
Rows 40 - 59: Same as Rows 21-38 but it's Adidas.

I will change the ctrl z now that you bring that up. I want the salesman to
push ctrl + q and all the qty's that are >= 1 should stay along with all the
Brand rows such as Row 20 and Row 39. That is if the salesman put a qty
under those brands. This was working great with the first macro that was
made, but then we needed to add more types of shoes for each brand, and now
the ones we added will not show up with the macro. Do you think there is a
way to update the macro or do I need to make a new spreadsheet and use the
suggestions from you or Bob? Thank you?

 

[Don Guillett]

Feel free to send me your workbook along with a re-iteration of the problem.
Send to the address below.

 

[john]

From the VBA helpfile:
Field Optional Variant. The integer offset of the field on which you want
to base the filter (from the left of the list; the leftmost field is field
one).

If you have not resolved your problem, try this approachchange Range &
Sheetname as required)

Sub Macro1()
'filter your list starting in cell A1 on Sheet1
'this should display only the entries in which field one
'equates to ">=1".
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1") '<< change sheet name
'as required
'clear filter
ws.AutoFilterMode = False
'filter range
ws.Range("A1").AutoFilter field:=1, _
Criteria1:=">=1", _
VisibleDropDown:=False
End Sub

 

[Bob Phillips]

If you want to filter column Y, yes.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)