Sorting block of rows with empty rows between it
this is how i do it....... this macro also contains shading every
other block, you could take that out. it's a horrible little macro
with selecting but it works. there's an "x" at the bottom of the
information (in the worksheet) to trigger the bottom of the
macro.......... maybe it will give you a place to start!
:)
========================
Option Explicit
Sub sorting_shading()
Dim MyRange As Variant
Dim LastCellRow As Long
Application.ScreenUpdating = False
On Error GoTo Arg
LastCellRow = Sheets("Category").Cells(16384, 1).End(xlUp).Row
Set MyRange = Range("a1:j" & LastCellRow)
MyRange.Interior.ColorIndex = xlNone
Range("$A$4").Select
Selection.CurrentRegion.Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Do
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, -1).Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Selection.Offset(1, 0).Select
If ActiveCell = ("x") Then
Exit Do
ElseIf ActiveCell < ("x") Then
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("A10"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Interior.ColorIndex = xlNone
End If
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, -1).Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Selection.Offset(1, 0).Select
If ActiveCell = ("x") Then
Exit Do
ElseIf ActiveCell < ("x") Then
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("A10"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
Loop
Range("$A$4").Select
Application.ScreenUpdating = True
Exit Sub
'=======================================
Arg:
MsgBox "An error has occured." _
& vbCrLf & _
vbCrLf & _
"Usually this is because there is more than one " _
& "blank line between the last data and the ""x""." _
& " Please check that there is only one blank line" _
& " and try again.", vbInformation + vbOKOnly
Exit Sub
'=============================
End Sub
======================
hope it helps!
susan
On Oct 2, 7:28*am, jgmiddel wrote:
In a overview I have:
In column A projectnumbers. Projectnumbers are sorted, but projects
can exist more then 1 time. So, a project can have reveral rows. In
the columns B-G there is information about the projects.
For example: in A1-5 there are projectnumbers, all the same.In B1-G5
information about all these projectparts. Row 6 is empty. In A7-A10
thera are also projectnumbers, in B7-G10 information. Row 11 is empty
again, and so on.
What I want is a option to sort these blocks. The order of the rows in
the project must remain.
Is this possible? Any help would be appriciated.
|