Others will be using this workbook so I need to build in the ability to add
new names (lines). I have a worksheet called "Names" and one called "Data".
The name field (defined as a group) becomes a drop down box on the Data
worksheet for easier data entry (one name may have multiple lines of data).
The problem arises when I make a macro to sort the names A - Z, the range of
the data is set to 1000 lines for future use despite the fact less than 50
lines are currently used. Sorting that many blank lines forces the 50 names
to the bottom of the list. How can I allow others the ability to use a "sort
button" yet allow for future growth?

Thanks in advance. Using Excel 2003

don't add extra line. Make your sort the exact number of lines.

get last row in your sort column. Line belwo uses column B
LastRow = cells(rows.count,"B").end(xlup).Row
set SortRange = Range("A1:F" & LastRow)

Then sort as follows
SortRange.Sort 'add the rest of your sort parameters here

"Tail Wind" wrote:

Others will be using this workbook so I need to build in the ability to add
new names (lines). I have a worksheet called "Names" and one called "Data".
The name field (defined as a group) becomes a drop down box on the Data
worksheet for easier data entry (one name may have multiple lines of data).
The problem arises when I make a macro to sort the names A - Z, the range of
the data is set to 1000 lines for future use despite the fact less than 50
lines are currently used. Sorting that many blank lines forces the 50 names
to the bottom of the list. How can I allow others the ability to use a "sort
button" yet allow for future growth?

Thanks in advance. Using Excel 2003

Thanks Joel.

Problem is I have never written actual code, using the macro recorder instead.

Do you have another suggestion or do I need to learn VBA?

Thanks again.


"Joel" wrote:

don't add extra line. Make your sort the exact number of lines.

get last row in your sort column. Line belwo uses column B
LastRow = cells(rows.count,"B").end(xlup).Row
set SortRange = Range("A1:F" & LastRow)

Then sort as follows
SortRange.Sort 'add the rest of your sort parameters here

"Tail Wind" wrote:

Others will be using this workbook so I need to build in the ability to add
new names (lines). I have a worksheet called "Names" and one called "Data".
The name field (defined as a group) becomes a drop down box on the Data
worksheet for easier data entry (one name may have multiple lines of data).
The problem arises when I make a macro to sort the names A - Z, the range of
the data is set to 1000 lines for future use despite the fact less than 50
lines are currently used. Sorting that many blank lines forces the 50 names
to the bottom of the list. How can I allow others the ability to use a "sort
button" yet allow for future growth?

Thanks in advance. Using Excel 2003

I often use the macro recorder. I don't have a great memory and often get
the syntex from the recorder and then make changes. Also a lot of VBA
functions are not well defined and using the recorder get the proper syntax.

Most recorded macros need some modifications. Recorded macros are not
general purpose macros, but work only on fixed cells and fixed ranges.
People who don't know VBA well post their code at this site to get the code
modified.

If you post your code, I would be glad to look at it a make the needed
modifications.

"Tail Wind" wrote:

Thanks Joel.

Problem is I have never written actual code, using the macro recorder instead.

Do you have another suggestion or do I need to learn VBA?

Thanks again.


"Joel" wrote:

don't add extra line. Make your sort the exact number of lines.

get last row in your sort column. Line belwo uses column B
LastRow = cells(rows.count,"B").end(xlup).Row
set SortRange = Range("A1:F" & LastRow)

Then sort as follows
SortRange.Sort 'add the rest of your sort parameters here

"Tail Wind" wrote:

Others will be using this workbook so I need to build in the ability to add
new names (lines). I have a worksheet called "Names" and one called "Data".
The name field (defined as a group) becomes a drop down box on the Data
worksheet for easier data entry (one name may have multiple lines of data).
The problem arises when I make a macro to sort the names A - Z, the range of
the data is set to 1000 lines for future use despite the fact less than 50
lines are currently used. Sorting that many blank lines forces the 50 names
to the bottom of the list. How can I allow others the ability to use a "sort
button" yet allow for future growth?

Thanks in advance. Using Excel 2003

Joel,

My code follows below. I have two sheets that must each be sorted to keep
the data together (there is likely a better way for this also but one problem
at a time!) so my idea was to make one macro that would sort both sheets
alike.
The data begins at A6 and goes thru column N. Above A6 are column headings.
The code below errors out on line 3.

Thank you so much for your generosity.



LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("A6:N" & LastRow)
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Corn Yields").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("A6:N" & LastRow)
Selection.Sort Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Policy Info").Select
End Sub

"Joel" wrote:

I often use the macro recorder. I don't have a great memory and often get
the syntex from the recorder and then make changes. Also a lot of VBA
functions are not well defined and using the recorder get the proper syntax.

Most recorded macros need some modifications. Recorded macros are not
general purpose macros, but work only on fixed cells and fixed ranges.
People who don't know VBA well post their code at this site to get the code
modified.

If you post your code, I would be glad to look at it a make the needed
modifications.

"Tail Wind" wrote:

Thanks Joel.

Problem is I have never written actual code, using the macro recorder instead.

Do you have another suggestion or do I need to learn VBA?

Thanks again.


"Joel" wrote:

don't add extra line. Make your sort the exact number of lines.

get last row in your sort column. Line belwo uses column B
LastRow = cells(rows.count,"B").end(xlup).Row
set SortRange = Range("A1:F" & LastRow)

Then sort as follows
SortRange.Sort 'add the rest of your sort parameters here

"Tail Wind" wrote:

Others will be using this workbook so I need to build in the ability to add
new names (lines). I have a worksheet called "Names" and one called "Data".
The name field (defined as a group) becomes a drop down box on the Data
worksheet for easier data entry (one name may have multiple lines of data).
The problem arises when I make a macro to sort the names A - Z, the range of
the data is set to 1000 lines for future use despite the fact less than 50
lines are currently used. Sorting that many blank lines forces the 50 names
to the bottom of the list. How can I allow others the ability to use a "sort
button" yet allow for future growth?

Thanks in advance. Using Excel 2003

The code was easy to fix. I just had to replace SELECTION with sortrange in
two places. The 2nd sort was also missing a KEY to perform the sorrt

Sub test()

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("A6:N" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Corn Yields").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("A6:N" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Policy Info").Select
End Sub


"Tail Wind" wrote:

Joel,

My code follows below. I have two sheets that must each be sorted to keep
the data together (there is likely a better way for this also but one problem
at a time!) so my idea was to make one macro that would sort both sheets
alike.
The data begins at A6 and goes thru column N. Above A6 are column headings.
The code below errors out on line 3.

Thank you so much for your generosity.



LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("A6:N" & LastRow)
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Corn Yields").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("A6:N" & LastRow)
Selection.Sort Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Policy Info").Select
End Sub

"Joel" wrote:

I often use the macro recorder. I don't have a great memory and often get
the syntex from the recorder and then make changes. Also a lot of VBA
functions are not well defined and using the recorder get the proper syntax.

Most recorded macros need some modifications. Recorded macros are not
general purpose macros, but work only on fixed cells and fixed ranges.
People who don't know VBA well post their code at this site to get the code
modified.

If you post your code, I would be glad to look at it a make the needed
modifications.

"Tail Wind" wrote:

Thanks Joel.

Problem is I have never written actual code, using the macro recorder instead.

Do you have another suggestion or do I need to learn VBA?

Thanks again.


"Joel" wrote:

don't add extra line. Make your sort the exact number of lines.

get last row in your sort column. Line belwo uses column B
LastRow = cells(rows.count,"B").end(xlup).Row
set SortRange = Range("A1:F" & LastRow)

Then sort as follows
SortRange.Sort 'add the rest of your sort parameters here

"Tail Wind" wrote:

Others will be using this workbook so I need to build in the ability to add
new names (lines). I have a worksheet called "Names" and one called "Data".
The name field (defined as a group) becomes a drop down box on the Data
worksheet for easier data entry (one name may have multiple lines of data).
The problem arises when I make a macro to sort the names A - Z, the range of
the data is set to 1000 lines for future use despite the fact less than 50
lines are currently used. Sorting that many blank lines forces the 50 names
to the bottom of the list. How can I allow others the ability to use a "sort
button" yet allow for future growth?

Thanks in advance. Using Excel 2003