Data sort

[VegasNath]

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?

[HansV]

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

[VegasNath]

Thanks Hans

[VegasNath]

Yes, you need to fill the blanks, then sort. You can do it like this:

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]

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

[VegasNath]

Thanks very much Hans.

[HansV]

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.