Excel macro dynamic range

Question

When recording macro my data is 4162 that why I have Range ("A1: A4162") each time I have data more than 4162 it will not work. I need code to pick any range of data I supply in the sheet

  Range("A1").Select
        Selection.AutoFilter
        ActiveWorkbook.Worksheets("Incident Ticket").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Incident Ticket").AutoFilter.Sort.SortFields.Add _
            Key:=Range("A1:A4162"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Incident Ticket").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

Answer 1

I added the function lastRow to be included in your code. This will automatically find the last row for you. Also, cleaned up your code a bit.

Sub Test()

    Dim ws As Worksheet, rng As Range
    Set ws = ThisWorkbook.Worksheets("Incident Ticket")
    Set rng = ws.Range("A1:A" & lastRow(ws))

    rng.AutoFilter
    With ws.AutoFilter
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlAscending, _
                DataOption:=xlSortNormal
        .Sort.Header = xlYes
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
    End With

End Sub

Function lastRow(ws As Worksheet, Optional col As Variant = 1) As Long
    With ws
        lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
End Function

Answer 2

You could simply specify “UsedRange” in your code – which will adjust no matter how big the data range becomes. The following code assumes you run the macro on the active sheet:

ActiveSheet.UsedRange.Sort _
key1:=ActiveSheet.Range("A1"), order1:=xlAscending, Header:=xlYes

Answer 3

If you are interested in only one column, try the following:

numRows = Sheets("Incident Ticket").Range("A1").End(xlDown).Row

If you are interested in the highest number row used in a given sheet, use the following

numRows = Sheets("Incident Ticket").UsedRange.Rows.Count

Sheets("Incident Ticket").AutoFilter.Sort.SortFields.Add _
   Key:=Range("A1:A" & numRows), SortOn:=xlSortOnValues, _
   Order:=xlAscending, DataOption:=xlSortNormal