PDA

View Full Version : Sorting a range of cells



infinity
04-07-2009, 09:38 AM
Hey vBaxers,

I have a question. I have a workbook that has code that builds a range of cells and formats them, adds formulas etc. The range starts out as 16 rows by 10 columns. There will be several of these ranges all of them formatted the same, some will have more rows added as time progresses but all will have the same number of columns. Is there a way that I can sort all the records alphabetically based on the value of one cell in each record without breaking the record apart? I know how to sort a range where there is only one row but not where there is multiple rows that need to stay together. Thanx for your help!

Infinity

nepotist
04-07-2009, 10:03 AM
ActiveWorkbook.Worksheets(sheetname).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(sheetname).Sort.SortFields.add Key:=Range("E:E"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets(sheetname).Sort
.SetRange Range("Specify your range")
.Header = xlYes 'chnage it as per your situation
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
If I was wrong in understanding your problem please be more clear

infinity
04-07-2009, 10:16 AM
Here is a workbook showing what I am trying to sort. The records I am trying to sort are from just below the first orange row to and including the next orange row, then the next record begins just below that row etc. The sorting would need to be based on the entry ("Pro Disposal", "CHFA" etc.) There is a lot in your code that I am unfamiliar with (which is not surprising), I am not sure if this will work with my situation. Thanx for the reply and let me know if you need more clarity.

Infinity

nepotist
04-07-2009, 10:26 AM
What I see is a user form(I mean a interface like that there inst any data to sort )

infinity
04-07-2009, 10:41 AM
Yes, it is sort of a userform (interface), when I attached the file it did not have the Excel icon, do you see the worksheet? If not I will try and resend it. What I want to be able to do, if possible, is take the entire range, in this case "Pro Disposal" and "CHFA" and sort the entire record for each in alphabetical order based on the name for the record... does that make sense?

In this example it would put everything that is currently in rows 25:40 and place it below "CHFA" because alphabetically CHFA comes before Pro Disposal. Maybe this is not possible, but if it is, it would make my world a whole lot easier because there could potentially be 25 or many more records on this sheet.

Infinity

nepotist
04-08-2009, 10:02 AM
Sorry :(

infinity
04-14-2009, 04:05 PM
That is quite alright, thank you anyway! Does anyone else know if this is possible? Thanx all!

Infinity:anyone:

mdmackillop
04-14-2009, 05:02 PM
Note: the Sort may need adjusted depending upon your Excel version

Option Explicit
Sub SortBlocks()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim rng As Range, cel As Range, c As Range
Dim i As Long, j As Long, Rw As Long

Set sh1 = Sheets("Bills")
Set sh2 = Sheets.Add

For i = 25 To 500 Step 16
j = j + 1
If sh1.Cells(i, 2) = "" Then
Exit For
Else
sh2.Cells(j, 1) = sh1.Cells(i, 2)
End If
Next

Set rng = sh2.Range("A1:A" & j - 1)
With ActiveWorkbook.Sheets(sh2.Name).Sort
.SortFields.Clear
.SortFields.Add Key:=rng, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rng
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Rw = 25
For Each cel In rng
Set c = sh1.Columns(2).Find(cel, lookat:=xlWhole)
Set c = c.Offset(, -1).Resize(16).EntireRow
'Skip if block is in correct position (Cut and paste will fail)
If c.Cells(1, 2).Value <> sh1.Cells(Rw, 2).Value Then
c.Cut
sh1.Cells(Rw, 1).Insert shift:=xlDown
End If
Rw = Rw + 16
Next

Application.DisplayAlerts = False
sh2.Delete
Application.DisplayAlerts = True

End Sub