Sort range must change with added lines

[Tail Wind]

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]

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]

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]

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]

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]

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]

Thank you very much. The macro runs great. Additional checking for
accuracy is coming yet but prelim indications are looking up.

Thank you for the one on one tutorial. You help make this a great forum for
wantabe's like me! I have learned I need to know more about VB to take my
skills to the next level.

Thanks again.

 

[Tail Wind]

Joel,

I'm hopeful you have email notification from this post still turned on.

The code properly sorts the "Policy Info" sheet but fails to sort all
columns in the "Corn Yields" sheet. In "Corn Yields", it sorts A & B but not
C or beyond. The upper left of the sort range is at A6. Columns go thru P.
I thought I would be able to get it but I'm stumped. I was hoping you would
kindly take a moment to check thru it once again.

Thanks in advance.


Sheets("Policy Info").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("A6" & 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, "C").End(xlUp).Row
Set SortRange = Range("A6" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Policy Info").Select
End Sub