Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 2nd 08, 12:28 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 10
Default 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   Report Post  
Old October 2nd 08, 01:38 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 1,117
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hpw do I delete multiple empty rows found between filled rows? Bill Excel Worksheet Functions 2 November 15th 09 08:12 PM
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows Scott Excel Worksheet Functions 0 December 13th 06 02:25 AM
Pivot Tables: How do I show ALL field rows, including empty rows?? [email protected] Excel Worksheet Functions 2 April 8th 05 06:21 PM
Can Excel "slide up" rows with content thru empty rows to condense portly44 Excel Worksheet Functions 2 April 1st 05 12:47 AM
select block of rows w/data between blank rows Janna Excel Programming 6 February 13th 05 03:45 AM


All times are GMT +1. The time now is 07:02 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017