Excel : Best way to select a range with a variable number of rows in a macro

PetGuy
PetGuy used Ask the Experts™
on
Need to set range from imported data where the number of rows varies.
PetzCoupons.xlsx
PetzCode.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You may try this.....
Dim LastRow As Long
'last row with data in any column on the sheet
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'last row with data in column E
LastRow = Cells(Rows.Count, "E").End(xlUp).Row

Open in new window

Top Expert 2016
Commented:
Hi,

pls try

    Dim endRow As Long, FullRange As String, RCRange As String
    Set Rng = Range("A1:E" & Range("E1").End(xlDown).Row)
    Rng.Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Rng.Offset(, 4).Resize(1, 1) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Rng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Names.Add Name:="Details", RefersTo:= _
        Rng.Address(False, False)
    ActiveWorkbook.Names("Details").Comment = ""

Open in new window

Regards

Commented:
Try this:

Dim FullRange as Range

Set FullRange=Range("A1").CurrentRegion

Open in new window

PetGuyCEO

Author

Commented:
Thank you for the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial