Results 1 to 2 of 2

Thread: Sort several ranges
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sort several ranges

    Hi everyone,

    I have several ranges in My worksheet. Each range is separated by an empty row .

    Each range start With A = 1050

    In all ranges A-H column are constant. And I-AT columns are variable.
    I need to sort for any range that I-AT columns will be sort by A->Z by columns I.

    First table below is wrong and the 2nd is correct.

    I will appreciate your help


    First table

    BUKRS BLART BLDAT BUDAT WAERS XBLNR BKTXT XMWST BSCHL/NEWBS HKONT/NEWKO NEWBK WRBTR
    1050 DZ 02.01.2018 02.01.2018 ILS RC189000002 40 112104 50
    11 7000002 50
    1050 DZ 02.01.2018 02.01.2018 ILS RC189000003 40 121009 18
    11 7000002 36
    40 121009 18
    2nd tabl
    BUKRS BLART BLDAT BUDAT WAERS XBLNR BKTXT XMWST BSCHL/NEWBS HKONT/NEWKO NEWBK WRBTR
    1050 DZ 02.01.2018 02.01.2018 ILS RC189000002 11 7000002 50
    40 112104 50
    1050 DZ 02.01.2018 02.01.2018 ILS RC189000003 11 7000002 36
    40 121009 18
    40 121009 18

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Sort several ranges

    Assuming your headers are in row 1 and the data starts in row 2, try this:

    Code:
    Sub Sort_ranges()
      Dim ar As Range, ini As Long
      Application.ScreenUpdating = False
      Range("I" & Rows.Count).End(xlUp)(3).Value = "x"
      ini = 2
      For Each ar In Range("I2", Range("I" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks)
        Range(Cells(ini, "I"), Cells(ar.Row - 1, "L")).Sort key1:=Range("I" & ini), order1:=xlAscending, Header:=xlNo
        ini = ar.Row + 1
      Next
      Range("I" & Rows.Count).End(xlUp).Value = ""
    End Sub
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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