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

Need to set range from imported data where the number of rows varies.
PetzCoupons.xlsx
PetzCode.txt

Microsoft Excel

Last Comment

Kevin Willyerd

8/22/2022 - Mon

Subodh Tiwari (Neeraj)

3/9/2016

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

ASKER CERTIFIED SOLUTION

Rgonzo1971

3/9/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Doug

3/9/2016

Try this:

Dim FullRange as Range

Set FullRange=Range("A1").CurrentRegion

Open in new window

Kevin Willyerd

3/9/2016

ASKER

Thank you for the help.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

Certified Expert Program

Credly Partnership

Udemy Partnership