Link to home
Create AccountLog in
Avatar of SameerMirza
SameerMirzaFlag for United Kingdom of Great Britain and Northern Ireland

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
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of SameerMirza

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
That does construct the range in VBA.
rStart is cell
What sohuld I expact Range("rStart").CurrentRegion to return?
The range representing all the data around rStart. The same range you would get if you selected the rStart cell and pressed Ctrl+A
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
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.

 
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
my bad for not guiding but thanks for help rorya