Problem Using Sort in macros

[Jacob]

I'm new to working with Excel macros, so if this is a dumb question, I
apologize. I was asked to automate view changes for a spreadsheet ( by
name, by date, by birthday, etc.) Using sort to accomplish the changes
worked quite well manually. The problem is that when sort is used in macro
(to make the procedure easier), it selects only what it selected at the time
the macro was written. If more lines have been added, it ignores them when
the macro is run. Is there a solution to this?

Thanks

 

[Norman Jones]

Hi Jacob,

Instead of sorting a static range, say, A1:A10 try something like:

Dim RngToSort as Range
Dim LastRow as Long

LastRow = Cells(Rows.Count, "A").End(xlUp).row

Set RngToSort = Range("A1:A" & LastRow)

RngToSort.Sort(...........).

If The data to be sorted resides in a table, you could also try:

Set RngToSort = Range("A1").CurrentRegion

 

[Ian G]

If you want to sort the entire sheet try the following

ActiveSheet.UsedRange.Sort Key1:=Range("A1")

That will sort all the data on the sheet by column A

Ian G