Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data sort (2002)

    Using code, I want to sort a range A2:L <last data row> by column L ascending. Some cells within column L are blank, and some are not. I want the blank cells to the top and the non blank cells below, but the macro recorder does not allow this, it always puts the non blanks above the blanks. Is there a workaround??

    Added later:

    Something like.....

    Use column I to find last data row.
    Sort A2 to L <last data row> by L. This leaves 2 sections of data, a section with data in L and a section without.
    Sort each section by J and K ascending.
    Move the section with data in L below the section without.
    somehow........

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data sort (2002)

    [whoooooooooooopdydoooooooo] Figured it out..... (sort of.....)

    Sub Sort()

    Dim ws1 As Worksheet
    Dim m1 As Long
    Dim m2 As Long
    Dim m3 As Long
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range

    Set ws1 = Worksheets("Rejections 2")

    m1 = ws1.Cells(ws1.Rows.Count, 9).End(xlUp).Row
    Set rng1 = ws1.Rows("2:" & m1)

    rng1.Select
    Selection.Sort Key1:=ActiveCell.Offset(0, 11).Range("A1"), Order1:= _
    xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation _
    :=xlTopToBottom, DataOption1:=xlSortTextAsNumbers

    m2 = ws1.Cells(ws1.Rows.Count, 12).End(xlUp).Row
    Set rng2 = ws1.Rows("2:" & m2)

    rng2.Select
    Selection.Sort Key1:=ActiveCell.Offset(0, 9).Range("A1"), Order1:= _
    xlAscending, Key2:=ActiveCell.Offset(0, 10).Range("A1"), Order2:=xlAscending _
    , Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
    xlTopToBottom, DataOption1:=xlSortTextAsNumbers, DataOption2:= _
    xlSortTextAsNumbers

    m3 = m2 + 1
    Set rng3 = ws1.Rows(m1 & ":" & m3)

    rng3.Select
    Selection.Sort Key1:=ActiveCell.Offset(0, 9).Range("A1"), Order1:= _
    xlAscending, Key2:=ActiveCell.Offset(0, 10).Range("A1"), Order2:=xlAscending _
    , Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
    xlTopToBottom, DataOption1:=xlSortTextAsNumbers, DataOption2:= _
    xlSortTextAsNumbers

    Selection.Cut
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select

    End Sub



    Sub Sort2()

    Dim ws1 As Worksheet
    Dim m4 As Long
    Dim rng4 As Range

    Set ws1 = Worksheets("Rejections 2")

    m4 = Range("L2").End(xlDown).Row - 1
    Set rng4 = ws1.Rows("2:" & m4)

    rng4.Select
    Selection.Sort Key1:=ActiveCell.Offset(0, 9).Range("A1"), Order1:= _
    xlAscending, Key2:=ActiveCell.Offset(0, 10).Range("A1"), Order2:=xlAscending _
    , Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
    xlTopToBottom, DataOption1:=xlSortTextAsNumbers, DataOption2:= _
    xlSortTextAsNumbers

    Range("A1").Select

    End Sub


    But....... Why the reference to Range("A1") within the sort's????

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data sort (2002)

    There's no need for Range("A1") in ActiveCell.Offset(0, 11).Range("A1") and similar. Since ActiveCell is a single cell, ActiveCell.Offset(0, 11) is a single cell to, so ActiveCell.Offset(0, 11).Range("A1") is equivalent to ActiveCell.Offset(0, 11).

  4. #4
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data sort (2002)

    <pre>Sub test()

    With ThisWorkbook.Sheets("Sheet1"): Rem adjust

    With Range(.Cells(2, 1), .Cells(.Rows.Count, 12).End(xlUp))

    Rem sort by column L
    .Sort Key1:=.Columns(12), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Rem cut/insert blank cell rows to the top
    Application.CutCopyMode = False
    On Error GoTo HaltRoutine
    Application.Intersect(.EntireColumn, _
    .Columns(12).SpecialCells(xlCellTypeBlanks).Entire Row).Cut
    .Range("a1").Insert shift:=xlDown

    HaltRoutine:
    On Error GoTo 0
    Application.CutCopyMode = False
    End With
    End With

    End Sub</pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •