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.

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

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

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