I refresh the data in my sheet frequently, and use a macro to sort it after importing.

When I Record the macro, it works the first time, but it has hardcoded Ranges.

    Sheets("Films").Select
    Range("A5:E5").Select
    Range(Selection, Selection.End(xlDown)).Select
    
    ActiveWorkbook.Worksheets("Films").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Films").Sort.SortFields.Add Key:=Range("E5:E3941") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Films").Sort
        .SetRange Range("A5:E3941")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Note that the Add Key and SetRange coordinates are fixed. The bottom Row will be incorrect the next time because there may be more, or fewer, imported rows.
What code do I need in the blank row to sense the bottom row, and how do I use it for the Add Key and SetRange specifications?

Any help would be very appreciated.

Moderators Note:
  • Please follow Forum Rule #3 and use code tags.
  • Added this time, but please use them in the future…Thanks.