filtering and macros

[gerry405]

Hi everyone,

I am new to excel and I need help with creation of a macro or macro's

I have a few problems, the first of them is that my spreadsheet gets
its data from an external source (set to automatically refresh on
open), I had managed to create a macro that populates my vlookups
columns, that seems to run fine as long as it doesn't go over certain
amount of lines(12,500), What I would really like is for it to just
populate down to the bottom of the data (which varies from day to day)
is there a command such as autofill, at the moment the macro remembers
how many lines I populated when I was recording it even though I double
click (which in essence is same as double click-autofill), which is not
what I was tring to do..

code below:
Sub sort1()
'
' sort1 Macro
' Macro recorded 08/09/2005 by SGUHT
'

'
Range("D2").Select
Selection.AutoFill Destination:=Range("D212500")
Range("D212500").Select
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F12500")
Range("F2:F12500").Select
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L12500")
Range("L2:L12500").Select
Range("A2:X2").Select
Range("X2").Activate
Range(Selection, Selection.End(xlDown)).Select
Range("A2:X12500").Select
Range("X2").Activate
Selection.sort Key1:=Range("U2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("Y2:Z2").Select
Selection.AutoFill Destination:=Range("Y2:Z12500")
Range("Y2:Z12500").Select
End Sub

The other thing is, I do filering on the data after its refreshed with
the new data, but I am having trouble with doing a more than one
filter

ie multiple xlOr's :

code below

Sub ASC_0_3_9()

Range("A1").AutoFilter Field:=15, Criteria1:="3", Operator:=xlOr, _
Criteria2:="5", Operator:=xlOr, _ Criteria3:="9"

End Sub

also, is there any way of putting lots of macros together, as I filter
on lots of criteria and although I have created macro's for each
filtering process, it's still a hassle to run these one by one,

...So basically, I am looking to be able to state in the code

If for example col2=F333 and col5=Gastro and col23 contains Joe blogs
then display found,
also
if col5=Gerry and col7=Tony
then also display found as well

 

[Dave Peterson]

One way is to pick out that column that defines the last row (I used column A).

Then use that in the destination range:

Option Explicit
Sub testme02()

Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("D2").AutoFill _
Destination:=.Range("d2:d" & LastRow)

End With

End Sub

And next time you do a custom filter, you'll notice that you get 2 criteria per
field--not 3.

I've used a helper column that contains a formula that evaluates to true/false
and then filtered on that:

=or(a2=2,a2=3,a2=4)
or
=OR(a2={2,3,9})

And if you want to filter a few different fields/columns:

with range("a1:Z999")
.AutoFilter Field:=7, Criteria1:="2"
.AutoFilter Field:=4, Criteria1:="3"
.AutoFilter Field:=3, Criteria1:="5"
end with

 

[gerry405]

Dave,

Thanks for your input, I still have the problem where I am looking for
more than one thing in a column and if it finds these I need to display
these only, I tried the below code as you suggested but it still does
not work.

I need to do a "multiple or" for more than just two criteria in a
specific column



Sub code()
With Range("a1:Z999")
..AutoFilter Field:=15, Criteria1:="9"
..AutoFilter Field:=15, Criteria1:="3"
..AutoFilter Field:=15, Criteria1:="5"
End With
End Sub

 

[Dave Peterson]

And next time you do a custom filter, you'll notice that you get 2 criteria per
field--not 3.

I've used a helper column that contains a formula that evaluates to true/false
and then filtered on that.



You may want to learn more about Data|Advanced filter, too. You can set up a
criteria range that allows you to specifiy lots of criteria.

Debra Dalgleish has some get started tips:
http://contextures.com/xladvfilter01.html