Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 Quote:
|
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
Dave,
Thanks, that now works a treat, now that I understand whats going on with that part of the code... Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering with a macro | New Users to Excel | |||
problems with macros | Excel Worksheet Functions | |||
display count/results of filtering Excel in status bar | Excel Discussion (Misc queries) | |||
Filtering out Data | Excel Discussion (Misc queries) |