Lewiy
Well-known Member
- Joined
- Jan 5, 2007
- Messages
- 4,284
Recently I’ve been involved in a number of Excel projects that have required sorting of data within macros, specifically on hidden worksheets.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
> </o>
As far as I know, the built-in VBA sort method only works on an active sheet which is less than ideal when you are working with hidden sheets. Yes, you can disable ScreenUpdating, unhide the sheet, sort the data and re-hide again, but this seems a bit of a waste of time and I’ve still experienced run time errors using this method so I’m growing weary of it.
<o> </o>
So, I’ve started investigating other methods of sorting data “manually”. The one I’m favouring at the moment is the bubble sort because of it’s reasonably straight forward implementation. This sort runs through each item of data, checks if each item is less than the one after it and if not, swaps them. It runs through this process continually until it does a complete pass through the data without making any swaps. The other benefit is that you can run this on a single column of data or even quite easily adapt it to sort a whole row based on the values in a column. The coding is quite simple:
<o> </o>
However, the only problem with this is that it can take a little while to run if there is a large amount of data. So I began looking at other methods of sorting such as the Bucket Sort, and Insertion Sort which both reduce run time, but have their issues. With the Bucket Sort, it was reasonably easy to sort a single column of data, but doing entire rows was much more complicated because of the nature of moving items into several groups (buckets), sorting each individually and then returning them to the main list. When using whole rows, this becomes a case of creating a worksheet for each “bucket”. The insertion sort would be fine, except that I had to create a custom function for identifying the relative position of items compared to the rest of the list when I was dealing with text strings (because functions like RANK don’t work on text).
<o> </o>
Does anyone have any suggestions as to other sort methods that might be more efficient and/or easier to use with whole rows of data? I’ve checked places like Wikipedia for Sort Methods, but many of these seem quite abstract and difficult to apply to an Excel environment.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o> </o>As far as I know, the built-in VBA sort method only works on an active sheet which is less than ideal when you are working with hidden sheets. Yes, you can disable ScreenUpdating, unhide the sheet, sort the data and re-hide again, but this seems a bit of a waste of time and I’ve still experienced run time errors using this method so I’m growing weary of it.
<o
> </o>So, I’ve started investigating other methods of sorting data “manually”. The one I’m favouring at the moment is the bubble sort because of it’s reasonably straight forward implementation. This sort runs through each item of data, checks if each item is less than the one after it and if not, swaps them. It runs through this process continually until it does a complete pass through the data without making any swaps. The other benefit is that you can run this on a single column of data or even quite easily adapt it to sort a whole row based on the values in a column. The coding is quite simple:
Rich (BB code):
Rich (BB code):
Rich (BB code):
Set Control = Sheets("Sheet1")<o:p></o:p>
With Control<o:p></o:p>
Limit = .Cells(Rows.Count, 1).End(xlUp).Row<o:p></o:p>
Do<o:p></o:p>
AnySwaps = False<o:p></o:p>
For c = 1 To Limit - 1<o:p></o:p>
If .Cells(c, 1) > .Cells(c + 1, 1) Then<o:p></o:p>
.Cells(Limit + 1, 1) = .Cells(c, 1)<o:p></o:p>
.Cells(c, 1) = .Cells(c + 1, 1)<o:p></o:p>
.Cells(c + 1, 1) = .Cells(Limit + 1, 1)<o:p></o:p>
.Cells(Limit + 1, 1) = ""<o:p></o:p>
AnySwaps = True<o:p></o:p>
End If<o:p></o:p>
Next c<o:p></o:p>
Loop Until AnySwaps = False<o:p></o:p>
End With<o
> </o>However, the only problem with this is that it can take a little while to run if there is a large amount of data. So I began looking at other methods of sorting such as the Bucket Sort, and Insertion Sort which both reduce run time, but have their issues. With the Bucket Sort, it was reasonably easy to sort a single column of data, but doing entire rows was much more complicated because of the nature of moving items into several groups (buckets), sorting each individually and then returning them to the main list. When using whole rows, this becomes a case of creating a worksheet for each “bucket”. The insertion sort would be fine, except that I had to create a custom function for identifying the relative position of items compared to the rest of the list when I was dealing with text strings (because functions like RANK don’t work on text).
<o
> </o>Does anyone have any suggestions as to other sort methods that might be more efficient and/or easier to use with whole rows of data? I’ve checked places like Wikipedia for Sort Methods, but many of these seem quite abstract and difficult to apply to an Excel environment.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
