Sort data

  • Thread starter Thread starter Rune_Daub
  • Start date Start date
R

Rune_Daub

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???
 
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
 
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)
 
Rune_Daub < said:
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???
Click to expand...

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,
 
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").?????
 
Rune_Daub < said:
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???
Click to expand...

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,
 
Back
Top