How to loop and sort blocks of rows
Category: [Excel] Demo Available
How to loop and sort blocks of rows
Requirement:
The user has a number of blocks of rows that are separated by an empty row.
Each block ranges from 2 rows up to 8 rows and there can be hundreds of these blocks all of varying size between 2 and 8.
In Colmn E of the each row the user has a number(could be any number) and wants to use this number to SORT each row out from lowest to highest.
Is there a way the user could loop through all of these blocks sorting each row out as it goes along?
Solution:
I have attached a file that I knocked up for testing the code. The first sheet has a header row and the 2nd sheet no header. Currently all blocks are sorted highest to lowest, change a value in Column E and that block will sort lowest to highest.
I have tweaked the codes a bit too
With header:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Aria, lCol As Long, r As Range, rCel As Range, i As Integer
With Application
.EnableEvents = 0
.ScreenUpdating = 0
End With
With ActiveSheet
lCol = .Rows(1).SpecialCells(2).Count
On Error GoTo ErrHndlr
For Each Aria In .UsedRange.Columns(1).SpecialCells(2).Areas
i = i + 1
For Each rCel In Target
Set r = Aria.Resize(, lCol)
If Not Intersect(Target, r.Columns(5)) Is Nothing Then
If i = 1 Then
r.Sort r.Columns(5), , , , , , , 1
Else
r.Sort r.Columns(5), , , , , , , 2
End If
End If
Next
Next
End With
Application.EnableEvents = 1
Exit Sub
ErrHndlr:
MsgBox "Unexpected error in the code", 16, "ERROR!"
Application.EnableEvents = 1
End Sub
No header
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Aria, lCol As Long, r As Range, rCel As Range
With Application
.EnableEvents = 0
.ScreenUpdating = 0
End With
With ActiveSheet
lCol = .Rows(1).SpecialCells(2).Count
On Error GoTo ErrHndlr
For Each Aria In .UsedRange.Columns(1).SpecialCells(2).Areas
For Each rCel In Target
Set r = Aria.Resize(, lCol)
If Not Intersect(Target, r.Columns(5)) Is Nothing Then
r.Sort r.Columns(5), , , , , , , 2
End If
Next
Next
End With
Application.EnableEvents = 1
Exit Sub
ErrHndlr:
MsgBox "Unexpected error in the code", 16, "ERROR!"
Application.EnableEvents = 1
End Sub
Try these codes in your actual file, if you still get an error then attach your file and explain exactly how the data gets updated.
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to sort worksheets based on a pre-sorted named-range |
| How to sort results after copying data from multiple sheets |
| How to sort values in positive and negative numbers with formula |
| How to sort by phone #, phone # in different columns, phone # with extensions |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.