Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofilter with macro
I am attempting to perform and autofilter in a macro.
i am using the following syntax and NOTHING happens when the Macro reaches this point. Im confused. If i record a macro of the actual action it looks like the syntax i use below. please help. Sheets("test").Select Range("B10:AL148").Sort Key1:=Range("E10"), Order1:=xlDescending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal Note that i want the sort to work regardless of teh number of vertical entries that are being sorted.. please help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofilter with macro
Can you pick out a column that always has data in it if that row is used?
And is there a reason you didn't include column A in the range to be sorted? There may be reasons, but it could be a mistake, too. This code uses column B to determine the last used row and only sorts columns B:AL. Dim LastRow as long with worksheets("test") lastrow = .cells(.rows.count,"B").end(xlup).row '<-- I used column B with .range("B10:AL" & lastrow) .sort key1:=.columns(4), order1:=xldescending, _ header:=xlno, ordercustom:=1, matchcase:=false, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal end with End with I specified no header. Change that to xlyes if you have headers in row 10. (I wouldn't let excel guess if it's my data!). And .columns(4) is the 4 column in B:AL (column E--same as your range("e10") stuff). If you use: with .range("A10:AL" & lastrow) Then you'd want .columns(5) to use column E. And if your original code was used in a commandbutton#_click event (or any code in a worksheet module), then check to see if the data in that sheet was sorted instead of the Test worksheet. (Untested, uncompiled. Watch for typos.) chuck wrote: I am attempting to perform and autofilter in a macro. i am using the following syntax and NOTHING happens when the Macro reaches this point. Im confused. If i record a macro of the actual action it looks like the syntax i use below. please help. Sheets("test").Select Range("B10:AL148").Sort Key1:=Range("E10"), Order1:=xlDescending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal Note that i want the sort to work regardless of teh number of vertical entries that are being sorted.. please help. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofilter with macro
ps. Your code showed you were sorting--not filtering.
Dave Peterson wrote: Can you pick out a column that always has data in it if that row is used? And is there a reason you didn't include column A in the range to be sorted? There may be reasons, but it could be a mistake, too. This code uses column B to determine the last used row and only sorts columns B:AL. Dim LastRow as long with worksheets("test") lastrow = .cells(.rows.count,"B").end(xlup).row '<-- I used column B with .range("B10:AL" & lastrow) .sort key1:=.columns(4), order1:=xldescending, _ header:=xlno, ordercustom:=1, matchcase:=false, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal end with End with I specified no header. Change that to xlyes if you have headers in row 10. (I wouldn't let excel guess if it's my data!). And .columns(4) is the 4 column in B:AL (column E--same as your range("e10") stuff). If you use: with .range("A10:AL" & lastrow) Then you'd want .columns(5) to use column E. And if your original code was used in a commandbutton#_click event (or any code in a worksheet module), then check to see if the data in that sheet was sorted instead of the Test worksheet. (Untested, uncompiled. Watch for typos.) chuck wrote: I am attempting to perform and autofilter in a macro. i am using the following syntax and NOTHING happens when the Macro reaches this point. Im confused. If i record a macro of the actual action it looks like the syntax i use below. please help. Sheets("test").Select Range("B10:AL148").Sort Key1:=Range("E10"), Order1:=xlDescending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal Note that i want the sort to work regardless of teh number of vertical entries that are being sorted.. please help. -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofilter with macro
Thanks Dave,
That was a ton of information that your provided me. I will have to absorb what you said and try it out... I will get back to you. chuck "Dave Peterson" wrote: ps. Your code showed you were sorting--not filtering. Dave Peterson wrote: Can you pick out a column that always has data in it if that row is used? And is there a reason you didn't include column A in the range to be sorted? There may be reasons, but it could be a mistake, too. This code uses column B to determine the last used row and only sorts columns B:AL. Dim LastRow as long with worksheets("test") lastrow = .cells(.rows.count,"B").end(xlup).row '<-- I used column B with .range("B10:AL" & lastrow) .sort key1:=.columns(4), order1:=xldescending, _ header:=xlno, ordercustom:=1, matchcase:=false, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal end with End with I specified no header. Change that to xlyes if you have headers in row 10. (I wouldn't let excel guess if it's my data!). And .columns(4) is the 4 column in B:AL (column E--same as your range("e10") stuff). If you use: with .range("A10:AL" & lastrow) Then you'd want .columns(5) to use column E. And if your original code was used in a commandbutton#_click event (or any code in a worksheet module), then check to see if the data in that sheet was sorted instead of the Test worksheet. (Untested, uncompiled. Watch for typos.) chuck wrote: I am attempting to perform and autofilter in a macro. i am using the following syntax and NOTHING happens when the Macro reaches this point. Im confused. If i record a macro of the actual action it looks like the syntax i use below. please help. Sheets("test").Select Range("B10:AL148").Sort Key1:=Range("E10"), Order1:=xlDescending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal Note that i want the sort to work regardless of teh number of vertical entries that are being sorted.. please help. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro, AutoFilter... | Excel Discussion (Misc queries) | |||
Autofilter Macro? | Excel Discussion (Misc queries) | |||
Autofilter from macro | Excel Discussion (Misc queries) | |||
Macro using autofilter | Excel Discussion (Misc queries) | |||
Autofilter Macro Help | Excel Discussion (Misc queries) |