Dynamic range

[Babalola]

I have the below code in macro... under the key range ("a1:a4162).. which meand during the record of my macro my data is up to 4162.

i need this to chnage if i populate my sheet with data with 5000


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

 

[dreid1011]

Perhaps something like this:

Dim lRow As Long
lRow = Sheets("Incident Ticket").Range("A1:A" & Rows.Count).End(xlUp).Row

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

 

[Jan Mach]

Why do all forum members sooo like as-long-as-Mississippi unreadable and slow constructions, why do you sooo hate easy-to-use, easy-to-read and quick sheet codenames?
Your sheet possesses its code name (Sheet1 or Sheet2 or Sheet156 ...). Give some convenient codename to the sheet, for example IncTi, and write:

​

Dim r As Range
With ActiveWorkbook.IncTi
    Set r = .[A1]
    r.AutoFilter
    Set r = [COLOR=#222222][FONT=Verdana]r.Resize(.Cells(.Rows[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#222222][FONT=Verdana].Count[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana], 1).End(xlUp).Row)[/FONT][/COLOR]<strike></strike>
    With .[COLOR=#222222][FONT=Verdana]AutoFilter[/FONT][/COLOR].Sort
        .SortFields.Clear
        [COLOR=#222222][FONT=Verdana].SortFields.[/FONT][/COLOR]Add r, xlSortOnValues, xlAscending, , xlSortNormal
[LEFT]        .Header = xlYes
[/LEFT]
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
[LEFT][COLOR=#222222][FONT=Verdana]End With
[/FONT][/COLOR][/LEFT]

​

 

[RoryA]

Why do you soooo like to post code that won't run and to denigrate other forum users? I strongly suggest you adjust your tone or you may find your participation here short-lived.