CODEWITHSUNDEEP
excelvba
BeachRunnerFred
BeachRunnerFred

Reputation: 18558

How can I only sort thru the rows with data?

I have a macro that sorts the rows by a specific column, the problem is I'm manually setting the range for the number of rows to sort (i.e. A2:A174) every time I add a new row. How can I change my code so that it sorts all the rows with data only so I don't have to go into the code and change the range every time I add a new row.

Sub SortByName()

  SortByName Macro

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A174") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:H174")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub
Sub SortByDate()

Thanks so much in advance for your wisdom!

Upvotes: 2

Views: 2333

Answers (1)

brettdj
brettdj

Reputation: 55672

something like this which looks for the last cell in A to mark the range. 

Sub SortByName()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Sheets("Sheet1")
    Set rng1 = ws.Range(ws.[a1], Cells(Rows.Count, "A").End(xlUp))
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=rng1 _
                                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange rng1.Resize(rng1.Rows.Count, 8)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.Goto ws.[a1]
End Sub

Upvotes: 3

Related Questions