SameerMirza

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

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

ASKER

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

That

*does*construct the range in VBA.ASKER

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

ASKER

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

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

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.

SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

my bad for not guiding but thanks for help rorya