Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort and Insert blank row (Excel 2003)

    I have a variable length column of data audit codes that I would like to Sort and then insert a blank row between each group of sorted codes. Attached is a sample of the codes. I tried recording a macro but that did not work . Can You help. The macro was

    Sub SortC()
    ' Sorts column C ascending with no header
    Range("C8:C66555").Select
    Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    End Sub
    Attached Files Attached Files

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

    Re: Sort and Insert blank row (Excel 2003)

    Worksheets in Excel 97 - 2003 have 65,536 rows, not 66,555.

    Try this version:

    Sub SortC()
    Dim n As Long
    Dim r As Long
    ' Last used row in column C
    n = Cells(Rows.Count, 3).End(xlUp).Row
    ' Sorts column C ascending with no header
    Range("C8:C" & n).End(xlUp).Sort Key1:=Range("C8"), Header:=xlNo
    ' Loop backwards through the cells
    For r = n To 9 Step -1
    ' If different from cell above...
    If Not Cells(r, 3) = Cells(r - 1, 3) Then
    ' ... insert a blank row
    Cells(r, 3).EntireRow.Insert
    End If
    Next r
    End Sub

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort and Insert blank row (Excel 2003)

    Hi Lawrence

    Try this code:
    <pre>Sub SortC()
    ' Sorts column C ascendingwith no header
    FinalRow = Range("C65536").End(xlUp).Row

    Range("C8:" & "C" & FinalRow).Select
    Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    End Sub
    </pre>


    Remember that there are only 65536 rows in Excel prior to 2007
    Jerry

  4. #4
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort and Insert blank row (Excel 2003)

    Wow !
    Thanks Hans Again.. and Again
    Lawrence

Posting Permissions

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