My worksheet consists of some fairly straight forward columns and I have
built macros with assigned Command Buttons to sort by their relative column.

This worksheet is a master worksheet of which copies are distributed to
several sales reps that will have varying numbers of rows that they will
populate but no more than 200 rows.

My intent is to modify my Macros so that they do not include blank rows in
the sorting - (i.e. if a sales reps' sheet only contains 175 rows, I always
want the blank rows (176-200) to remain at the bottom.

Here is an example of one of my sorting macros for one of the columns:

Sub Sort_Rank()
Range("A10:V210").Select
Selection.Sort Key1:=Range("A10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I should point out that the range only includes the rows and columns of data
- it does not include column headers

Thanks for your help in advance,

Scott

Can you pick out a column that's always used if that row is used?

I'm gonna choose column A.

dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
with .range("a10:V" & lastrow
.sort key1:=.columns(1), order1:=xlascending, _
header:=xlno, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
end with
end with

ScottPcola wrote:

My worksheet consists of some fairly straight forward columns and I have
built macros with assigned Command Buttons to sort by their relative column.

This worksheet is a master worksheet of which copies are distributed to
several sales reps that will have varying numbers of rows that they will
populate but no more than 200 rows.

My intent is to modify my Macros so that they do not include blank rows in
the sorting - (i.e. if a sales reps' sheet only contains 175 rows, I always
want the blank rows (176-200) to remain at the bottom.

Here is an example of one of my sorting macros for one of the columns:

Sub Sort_Rank()
Range("A10:V210").Select
Selection.Sort Key1:=Range("A10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I should point out that the range only includes the rows and columns of data
- it does not include column headers

Thanks for your help in advance,

Scott

--

Dave Peterson