filtering and macros
 

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

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 wrote:

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

--
gerry405

--

Dave Peterson

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


--
gerry405
------------------------------------------------------------------------
gerry405's Profile: http://www.excelforum.com/member.php...o&userid=27939
View this thread: http://www.excelforum.com/showthread...hreadid=474278

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



gerry405 wrote:

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

--
gerry405
------------------------------------------------------------------------
gerry405's Profile: http://www.excelforum.com/member.php...o&userid=27939
View this thread: http://www.excelforum.com/showthread...hreadid=474278

--

Dave Peterson

thanks Dave,


I have tried the populating code as you suggested, it works fine for one column(only), but I can't adjust it to do more than one column, partly because I don't understand what your code is doing, more so at the part below

ie

.Range("D2").AutoFill _
Destination:=.Range("d2:d" & LastRow)
what does "d2:d2 stand for

When I run my macro I would like to populate more than one column so I've tried to adjust to no avail, I thought I could do the following with your code:

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)

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

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

End With

End Sub

But this does not work, I expect you'll know why, but to me it's double dutch

thanks Dave,


I have tried the populating code as you suggested, it works fine for one column(only), but I can't adjust it to do more than one column, partly because I don't understand what your code is doing, more so at the part below

ie

.Range("D2").AutoFill _
Destination:=.Range("d2:d" & LastRow)
what does "d2:d2 stand for

When I run my macro I would like to populate more than one column so I've tried to adjust to no avail, I thought I could do the following with your code:

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)

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

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

End With

End Sub

But this does not work, I expect you'll know why, but to me it's double dutch

I'm guessing that this:
what does "d2:d2 stand for
was a typo

and you really meant:
what does "d2:d" stand for

It's easier to see with this:

Range("d2:d" & LastRow)

If the lastrow was 12345 (just for talking purposes), then

you'd get: Range("d2:d" & 12345)
or
Range("d2:d12345")
Which is column D rows 2:12345.

So I'm betting you really meant something like:

Range("F2").AutoFill _
Destination:=.Range("F2:F" & LastRow)

Range("L2").AutoFill _
Destination:=.Range("L2:L" & LastRow)

You start in L2 and finish with the last cell in column L
(lastrow=lastusedrow in column A)



gerry405 wrote:

thanks Dave,

I have tried the populating code as you suggested, it works fine for
one column(only), but I can't adjust it to do more than one column,
partly because I don't understand what your code is doing, more so at
the part below

ie

Range("D2").AutoFill _
Destination:=.Range("d2:d" & LastRow)
what does "d2:d2 stand for

When I run my macro I would like to populate more than one column so
I've tried to adjust to no avail, I thought I could do the following
with your code:

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)

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

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

End With

End Sub

But this does not work, I expect you'll know why, but to me it's double
dutch

--
gerry405

--

Dave Peterson

Dave,

Thanks, that now works a treat, now that I understand whats going on with that part of the code...