Sorting data

M

Mikey C

Hi all

This is kind of related to my earlier post. I have found a way to sort
my data which contains merged cells. It's a bit cumbersome but seems
to do the trick.

The only thing I am now stuck on is a way to ignore tasks which have
already been completed (in the sort), so that they will be pushed to
the bottom of the list, whilst the active tasks will be sorted by due
date order.

I have used the following code / macro:

Private Sub CommandButton1_Click()
'
' ResizeNotes Macro
' Macro recorded 17/09/2007 by michaelc
'

'
Cells.Select
Cells.EntireRow.AutoFit
Range("A27").Select

'
' TestSorting Macro
' Macro recorded 17/09/2007 by michaelc
'

'
Range("C28:F28").Select
Selection.UnMerge
Selection.Copy
Range("C29:F1000").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A28:G1000").Select
ActiveWindow.ScrollRow = 976
ActiveWindow.ScrollRow = 974
ActiveWindow.ScrollRow = 972
ActiveWindow.ScrollRow = 970
ActiveWindow.ScrollRow = 966
ActiveWindow.ScrollRow = 962
ActiveWindow.ScrollRow = 955
ActiveWindow.ScrollRow = 947
ActiveWindow.ScrollRow = 943
ActiveWindow.ScrollRow = 937
ActiveWindow.ScrollRow = 929
ActiveWindow.ScrollRow = 921
ActiveWindow.ScrollRow = 912
ActiveWindow.ScrollRow = 902
ActiveWindow.ScrollRow = 888
ActiveWindow.ScrollRow = 875
ActiveWindow.ScrollRow = 857
ActiveWindow.ScrollRow = 843
ActiveWindow.ScrollRow = 830
ActiveWindow.ScrollRow = 812
ActiveWindow.ScrollRow = 799
ActiveWindow.ScrollRow = 779
ActiveWindow.ScrollRow = 763
ActiveWindow.ScrollRow = 746
ActiveWindow.ScrollRow = 726
ActiveWindow.ScrollRow = 707
ActiveWindow.ScrollRow = 691
ActiveWindow.ScrollRow = 674
ActiveWindow.ScrollRow = 654
ActiveWindow.ScrollRow = 635
ActiveWindow.ScrollRow = 615
ActiveWindow.ScrollRow = 596
ActiveWindow.ScrollRow = 576
ActiveWindow.ScrollRow = 557
ActiveWindow.ScrollRow = 533
ActiveWindow.ScrollRow = 510
ActiveWindow.ScrollRow = 490
ActiveWindow.ScrollRow = 471
ActiveWindow.ScrollRow = 451
ActiveWindow.ScrollRow = 428
ActiveWindow.ScrollRow = 409
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 370
ActiveWindow.ScrollRow = 348
ActiveWindow.ScrollRow = 327
ActiveWindow.ScrollRow = 305
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
Selection.Sort Key1:=Range("B28"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("C28:F28").Select
Selection.Merge
Selection.Copy
Range("C29:F1000").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A28").Select
ActiveWindow.SmallScroll Down:=-27
Range("A1:D1").Select


End Sub

Private Sub CommandButton2_Click()
'
' TestSorting Macro
' Macro recorded 17/09/2007 by michaelc
'

'
Range("C28:F28").Select
Selection.UnMerge
Selection.Copy
Range("C29:F1000").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A28:G1000").Select
ActiveWindow.ScrollRow = 976
ActiveWindow.ScrollRow = 974
ActiveWindow.ScrollRow = 972
ActiveWindow.ScrollRow = 970
ActiveWindow.ScrollRow = 966
ActiveWindow.ScrollRow = 962
ActiveWindow.ScrollRow = 955
ActiveWindow.ScrollRow = 947
ActiveWindow.ScrollRow = 943
ActiveWindow.ScrollRow = 937
ActiveWindow.ScrollRow = 929
ActiveWindow.ScrollRow = 921
ActiveWindow.ScrollRow = 912
ActiveWindow.ScrollRow = 902
ActiveWindow.ScrollRow = 888
ActiveWindow.ScrollRow = 875
ActiveWindow.ScrollRow = 857
ActiveWindow.ScrollRow = 843
ActiveWindow.ScrollRow = 830
ActiveWindow.ScrollRow = 812
ActiveWindow.ScrollRow = 799
ActiveWindow.ScrollRow = 779
ActiveWindow.ScrollRow = 763
ActiveWindow.ScrollRow = 746
ActiveWindow.ScrollRow = 726
ActiveWindow.ScrollRow = 707
ActiveWindow.ScrollRow = 691
ActiveWindow.ScrollRow = 674
ActiveWindow.ScrollRow = 654
ActiveWindow.ScrollRow = 635
ActiveWindow.ScrollRow = 615
ActiveWindow.ScrollRow = 596
ActiveWindow.ScrollRow = 576
ActiveWindow.ScrollRow = 557
ActiveWindow.ScrollRow = 533
ActiveWindow.ScrollRow = 510
ActiveWindow.ScrollRow = 490
ActiveWindow.ScrollRow = 471
ActiveWindow.ScrollRow = 451
ActiveWindow.ScrollRow = 428
ActiveWindow.ScrollRow = 409
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 370
ActiveWindow.ScrollRow = 348
ActiveWindow.ScrollRow = 327
ActiveWindow.ScrollRow = 305
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
Selection.Sort Key1:=Range("B28"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("C28:F28").Select
Selection.Merge
Selection.Copy
Range("C29:F1000").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A28").Select
ActiveWindow.SmallScroll Down:=-27
Range("A1:D1").Select

End Sub


Can anybody suggest how I could somehow exclude complete tasks from
the sort?

Thanks for you help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Error 6
Run Macros 11
variable number of rows. 3
Recorded macro 4
Macro that deletes and pastes filterd rows and then some 2
Problem with Macro 7
Need Help with VBA code for an Excel Model Macro 5
Macro code 0

Top