PDA

View Full Version : Solved: VBA Sort



danesrood
05-27-2009, 06:59 AM
Dear All

Can I please ask for your help in providing an efficient option on my worksheet to sort two blocks of data by either of the last two columns.

The first block of data will always start at A3 and consist of about two hundred rows and up to about 15 columns. Column A is text with all other columns being numerical.

There will then be one empty row followed by a much smaller group of data but structured exactly the same. as above with the same headings. This will probably only be about 12 rows.

What I would like to do is to be able to sort the two blocks of data at the same time by either one of of the last two columns which are headed "Rank on YTD" and "Rank Change This Month"

I hope that I haven't made this sound too complicated.

As always my thanks for any help that you can provide.

Danesrood

Bob Phillips
05-27-2009, 09:24 AM
So are you wanting to mix the second block into the first block? And which column, either one of is a bit vague.

mdmackillop
05-27-2009, 09:54 AM
If I understand.

danesrood
05-27-2009, 11:15 AM
mdmackillop
Thank you so much for taking the time to help me.
The example file provided is pretty much as it is, other than column A is text and the block starts with the headings on row3. Is this an issue.

Button 1 seems to be working fine and is sorting the two blocks of data independently on the YTD column but Button 2 is only sorting the second block on Rank Change This Month rather than both.

Again my thanks

Danesrood

mdmackillop
05-27-2009, 12:09 PM
Check that the headers are exactly the same and are properly entered in the code. As you can see, the code is exactly the same for both headers.

mdmackillop
05-27-2009, 12:13 PM
A neater version

Option Explicit

Sub DoSort1()
DoSort "Rank on YTD"
End Sub

Sub DoSort2()
DoSort "Rank Change This Month"
End Sub

Sub DoSort(Txt As String)
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = Cells.Find(Txt)
Set Rng2 = Rng1.CurrentRegion
Rng2.Sort key1:=Rng1.Offset(1), Header:=xlYes
Set Rng1 = Cells.Find(Txt, after:=Rng1)
Set Rng2 = Rng1.CurrentRegion
Rng2.Sort key1:=Rng1.Offset(1), Header:=xlYes
End Sub

danesrood
05-28-2009, 01:39 AM
mdmackillop

Yes you are quite right I made a slight adjustment to one of the headings. It is working fine now. Thank you so much

Could I just ask a couple of questions to understand its limitations and how it works:

1) Assuming that I have no blank entries in the left hand column which is in fact cities and I don't ever think it would happen can I have empty cells elsewhere and for the sake of this discussion the extreme bottom right hand cell. Would it still pick up the full extent of the range to be sorted.

2) Can I change the code to sort in reverse order as I have another sheet that requires this.

3) Can you explain what the offset does.

Again my sincere thanks for taking the time to help me, it is much appreciated.

Danesrood

mdmackillop
05-28-2009, 04:23 AM
1) Give it a try. You must have a clear row between blocks for CurrentRegion to work
2) You can expand on the Find code with regard to SearchDirection and Start position which will allow reveresed order. Check the VBA Help file.
3) Offset refers to a a range offset from the original, by (Rows,Columns)
(Range"A1").Offset(5,4) refers to F5 (negative numbers can also be used). Combining this with Resize(Rows,Columns) gives a very convenient way to specify ranges.

danesrood
05-28-2009, 05:25 AM
Will do.

Thank you again


Many thanks

Danesrood