CODEWITHSUNDEEP
vbaexcel
Babalola Naheem Ganiyu
Babalola Naheem Ganiyu

Reputation: 21

Excel macro dynamic range

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

Upvotes: 2

Views: 1982

Answers (3)

user3259118
user3259118

Reputation:

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

Upvotes: 1

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

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

Upvotes: 1

RogueScion
RogueScion

Reputation: 11

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

Upvotes: 0

Related Questions