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

K.Dᴀᴠɪs · Accepted Answer · 2018-03-09 22:02:21Z

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

answered Mar 9, 2018 at 22:02

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3259118 · Accepted Answer · 2018-03-10 22:52:02Z

1

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

answered Mar 10, 2018 at 22:52

user3259118

Comments

RogueScion · Accepted Answer · 2018-03-09 21:35:45Z

0

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

answered Mar 9, 2018 at 21:35

RogueScion

112 bronze badges

1 Comment

Babalola Naheem Ganiyu Over a year ago

i didnt really get the answer

Collectives™ on Stack Overflow

Excel macro dynamic range

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related