asked on # sort dynamic excel range - VBA

Hi,

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.

thanks

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.

thanks

Microsoft Excel

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

then go to Select Data Menue-> then Select Sort

rorya,

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

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.

rwheeler23

That *does *construct the range in VBA.

rStart is cell

What sohuld I expact Range("rStart").CurrentRegion to return?

What sohuld I expact Range("rStart").CurrentReg

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.

exactly.

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

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

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

or

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
True.

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.

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.

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