Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting block of rows with empty rows between it
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions | |||
Can Excel "slide up" rows with content thru empty rows to condense | Excel Worksheet Functions | |||
select block of rows w/data between blank rows | Excel Programming |