Macro Help

[Michael Koerner]

I have a macro which sorts a shopping list in the order of the stores that I
do my grocery shopping, that I created using the create a macro option in I
believe 2003, now using 2007, code below. Unfortunately if I add more items
to the list and forget to change the macro end range things don't get sorted
in the proper order. Is there any way that I can change the range so that
regardless of the number of items in the list the macro will work properly?

Sub SortShoppingList()
'
' SortShoppingList Macro
' Macro recorded 15/12/2006 by Michael Koerner
'

'
Range("A1").Select
Selection.AutoFilter Field:=4, Criteria1:="<>"
Range("A1:G1741").Sort Key1:=Range("E2"), Order1:=xlDescending, Key2:= _
Range("B2"), Order2:=xlAscending, Key3:=Range("C2"),
Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
_
DataOption3:=xlSortNormal
End Sub

 

[John Bundy]

There are a few methods, this probably being somewhere in the middle, and
good to know
Dim lastRow As Long
lastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

Change the Sheet1 to the one you need and this will find the las row
containing data in column "A". Now just plug this into your code further down:
Range("A1:" & lastRow).

 

[john]

You had some resonses to this question in your first post.

 

[Michael Koerner]

Sorry about that. It did not show up when I first checked for it

--

Regards
Michael Koerner


You had some resonses to this question in your first post.

 

[Michael Koerner]

Thanks John, will give it a try and sorry about the double post.

--

Regards
Michael Koerner


There are a few methods, this probably being somewhere in the middle, and
good to know
Dim lastRow As Long
lastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

Change the Sheet1 to the one you need and this will find the las row
containing data in column "A". Now just plug this into your code further
down:
Range("A1:" & lastRow).

 

[Patrick Molloy]

and another ....

Range(Range("A1"), Range("G1").End(xlDown) ).Sort Key1:=Range("E2"),
Order1:=xlDescending, Key2:= Range("B2"), Order2:=xlAscending,
Key3:=Range("C2"), Order3:=xlAscending