CODEWITHSUNDEEP
excelvba
Deke
Deke

Reputation: 495

Sorting from a dynamic row

I'm working on sorting two sections of a spreadsheet the top works fine the bottom half I'm having a problem with since the row changes based on the data. Here is what I have, the problem is it ends up sorting the entire (both) sections (which are separated by a line break). Any help in the right direction would be greatly appreciated.

Edit for Clarification: what I have is a spreadsheet that has two sections. The top section which can be a variable/dynamic range. IE it could be 30 rows or 300 rows. The second half of the spreadsheet which is separated by a line insert (blank row of cells) I need to sort and am having the issue. The problem is getting the sort to recognize the cells from A (variable/dynamic) row to CU. Hopefully that helps explain things a little better but just let me know if more clarification is needed. 

 Sub test()

 Dim lrow As Long

 lrow = Selection.End(xlDown).Row

Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Inseason Columns").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Inseason Columns").Sort.SortFields.Add Key:=Range( _
    "E" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Inseason Columns").Sort
    .SetRange Range("A:CU", Selection.End(xlDown))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Upvotes: 0

Views: 79

Answers (1)

user10808941
user10808941

Reputation:

If the 'line breaks' are blank rows, hide them and you can cycle through the Range.Areas of Range.SpecialCells(xlCellTypeVisible).

Option Explicit

Sub sortAreas()

    Dim a As Long

    With Worksheets("sheet10")

        .range("1:2").entirerow.hidden = true
        .Columns("E").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

        With Intersect(.Range("A:CU"), .UsedRange).SpecialCells(xlCellTypeVisible)

            For a = 1 To .Areas.Count

                With .Areas(a)

                    .Cells.Sort Key1:=.Columns(5), Order1:=xlAscending, DataOption1:=xlSortTextAsNumbers, _
                                Orientation:=xlTopToBottom, Header:=xlNo

                End With
            Next a

        End With

        .Columns("E").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
        .range("1:2").entirerow.hidden = false

    End With

End Sub

Upvotes: 2

Related Questions