Sort data
 

If I record a macro using the macro recorder. Then I get this
recording.

Sub Makro1()
Range("A1").Select
Range("A1:Q965").Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range _
("L2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End Sub

Since my datasheet continues to get larger, I need the
Range("A1:......) to get larger as well.. How do I set it up to check
for the entire datasheet, so that I make sure that the entire sheet is
sorted in the progress???


---
Message posted from http://www.ExcelForum.com/

Sort data
 

one way:

If your data is contiguous (No completely empty rows or columns) you can
let XL figure it out:

Sub Makro1()
Range("A1").Sort _
Key1:=Range("A1"), _
Order1:=xlAscending, _
Key2:=Range("L2"), _
Order2:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

Another way, if there is a value in column 1 for all filled rows:

Range("A1:Q" & Range("A" & Rows.Count).End(xlUp).Row).Sort _
Key1:=Range("A1"), _
Order1:=xlAscending, _
Key2:=Range("L2"), _
Order2:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom


In article ,
Rune_Daub wrote:

If I record a macro using the macro recorder. Then I get this
recording.

Sub Makro1()
Range("A1").Select
Range("A1:Q965").Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range _
("L2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End Sub

Since my datasheet continues to get larger, I need the
Range("A1:......) to get larger as well.. How do I set it up to check
for the entire datasheet, so that I make sure that the entire sheet is
sorted in the progress???


---
Message posted from http://www.ExcelForum.com/

Sort data
 

Hi,
Try,

Sub Makro1()
Range("A1").Select
Range("A1").currentregion.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range _
("L2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End Sub


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
(Excel Add-ins)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sort data
 

Rune_Daub < wrote:
If I record a macro using the macro recorder. Then I get this
recording.

Sub Makro1()
Range("A1").Select
Range("A1:Q965").Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range _
("L2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End Sub

Since my datasheet continues to get larger, I need the
Range("A1:......) to get larger as well.. How do I set it up to check
for the entire datasheet, so that I make sure that the entire sheet is
sorted in the progress???

You can use the UsedRange property:

Sub Makro1()
ActiveSheet.UsedRange.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range _
("L2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End Sub

Be careful though, because UsedRange takes into account all the cells
that has been formatted, even if there is no data in them.

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

Sort data
 

My excel sheet consists of 15 columns with headings.
I have 500 rows of information. Not all fields are filled out.
I have formatted 2000 rows to show if an error has occured in th
entered data.

So I cant use the first response by Beto allthough it was a good one
thx...
I cant use the second one by JE McGimpsey cause I cant guarentie tha
all rows and collumns at this point contain information. It will in th
long run.. but not for the next few months.
Shailesh Shah.. wont the currentregion just sort the data until th
first empty cell is encountered in column A???

I know how many columns there are in the sheet... A to Q
Cant we somehow test the last row that has information entered, an
then enter those information into the Range("A1:Q965").?????

--
Message posted from http://www.ExcelForum.com

Sort data
 

Rune_Daub < wrote:

My excel sheet consists of 15 columns with headings.
I have 500 rows of information. Not all fields are filled out.
I have formatted 2000 rows to show if an error has occured in the
entered data.
Shailesh Shah.. wont the currentregion just sort the data until the
first empty cell is encountered in column A???

No, I think CurrentRegion will do. If it don't then I still don't
understand the layout. CurrentRegion will stop when it finds a whole
empty row.

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.