I need a macro that will sort the attached data by E, then C.
My guess is that in it's current format, this is not possible, please correct me if I am wrong
I am thinking that I would need to fill all of the blanks (Columns C, D & E) down from the row above and then sort. If this is the case, how would be the best approach to tackle this, else, any better suggestions?
Data sort
-
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Data sort
Yes, you need to fill the blanks, then sort. You can do it like this:
Code: Select all
Sub SortEm()
With Range("C6:E118")
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
Range("C5:H118").Sort Key1:=Range("E5"), Key2:=Range("C5"), Header:=xlYes
End Sub
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Data sort
Hans, Is there an easy way to remove all the 'filler' data following the sort? Something like looping through the three columns, top to bottom and clear contents if the cell = the cell above?HansV wrote:Yes, you need to fill the blanks, then sort. You can do it like this:
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Data sort
You have to work from bottom to top because if you clear a cell, the test for the cell in the next row will fail (think about it for a moment...)
Code: Select all
Dim r As Long
Dim c As Long
For r = 118 To 6 Step -1
For c = 3 To 5
If Cells(r, c) = Cells(r - 1, c) Then
Cells(r, c).ClearContents
End If
Next c
Next r
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
-
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Data sort
If you apply this to a large range, you may want to insert
Application.ScreenUpdating = False
at the beginning of the code, and
Application.ScreenUpdating = True
at the end.
Application.ScreenUpdating = False
at the beginning of the code, and
Application.ScreenUpdating = True
at the end.
Best wishes,
Hans
Hans