Thread: Macro help
View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
John John is offline
external usenet poster
 
Posts: 2,069
Default Macro help

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 approach:(change 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

--
jb


"N. McCain" wrote:

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

"N. McCain" wrote:

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