Avatar of SameerMirza
Flag for United Kingdom of Great Britain and Northern Ireland asked on

sort dynamic excel range - VBA


I need to sort excel range based on a data column in assending order.
parameters are,
number of rows: undefined/dynamic (so may be we can go use xlEnd or something)
First cell in the range in named : rStart
And it goes upto column 'O'.

It would be great if some one could help me out with it.

Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon
Rory Archibald

Which column do you want to sort on? You can use Range("rStart").Currentregion to get the tabular range that rStart is in.

Select which Column you need to sort
then go to Select Data Menue-> then Select  Sort

Its first cell of the range. So I have to constrct the actual range in VBA.
I am sure it is do able with with parameters.
I have used the code before :) - just dont remember
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.
Rory Archibald

That does construct the range in VBA.

rStart is cell
What sohuld I expact Range("rStart").CurrentRegion to return?
Rory Archibald

The range representing all the data around rStart. The same range you would get if you selected the rStart cell and pressed Ctrl+A
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

This is not what I am after.
I wanted to go from this cell to the column 'O'
and then all the way down 65530 rows. (or may be a little less)

But I found another way which is something like ,
Name a header range
and use something like
ImportHeader = NameOfTheHeaderRange
rows = MaxRows
With ImportHeader.Resize(rows)
            .Sort Key1:="Date", Order1:=xlAscending, Header:=xlYes
        End With
Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

But .CurrentRegion wouldnt work in this case because of the formatting.
My understanding it that it select all the data around the range but I dont want that.
And its bounded by blank row and column which wouldnt work either.

There however is another way around here to skip the rows.
Which is using xlEnd property and find the number of occupied rows. - dont exactly remember the code line.

Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

my bad for not guiding but thanks for help rorya
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes