How to generalize sorting range in macro???
 

Hi,

I have recorded a macro which would help me to sort a list of data. First,
when I have done with the recording, I found out that the range of the data
list is being hard coded in the marco, for example, Range("A1:F30").Sort
.......

But what happen is I may have different ranges of lists! Sometimes, the list
may be shorten then the previous one but sometimes may be longer then. So, I
tried to replace the fixed range with a Range Object variable; but it doesn't
work and the system generated a debug!!!

How actually to solve this problem????
Please advise........

Thanking in advance!!

How to generalize sorting range in macro???
 

If you like Excel to identify the range, then just use a single variable in
the range - preferrable the index range

With Range("D1")
.Sort Key1:=.Cells(1,1), Order1:=xlAscending
End With

or
Dim rng as Range
set rng = Range("A1").CurrentRegion

rng.Sort Key1:=rng(1).Offset(0,3), Order1:=xlAscending


--
Regards,
Tom Ogilvy


"Jac" wrote:

Hi,

I have recorded a macro which would help me to sort a list of data. First,
when I have done with the recording, I found out that the range of the data
list is being hard coded in the marco, for example, Range("A1:F30").Sort
......

But what happen is I may have different ranges of lists! Sometimes, the list
may be shorten then the previous one but sometimes may be longer then. So, I
tried to replace the fixed range with a Range Object variable; but it doesn't
work and the system generated a debug!!!

How actually to solve this problem????
Please advise........

Thanking in advance!!

How to generalize sorting range in macro???
 

Hi Tom,

Thanks for your help..............
It's working now! ; )



"Tom Ogilvy" wrote:

If you like Excel to identify the range, then just use a single variable in
the range - preferrable the index range

With Range("D1")
.Sort Key1:=.Cells(1,1), Order1:=xlAscending
End With

or
Dim rng as Range
set rng = Range("A1").CurrentRegion

rng.Sort Key1:=rng(1).Offset(0,3), Order1:=xlAscending


--
Regards,
Tom Ogilvy


"Jac" wrote:

Hi,

I have recorded a macro which would help me to sort a list of data. First,
when I have done with the recording, I found out that the range of the data
list is being hard coded in the marco, for example, Range("A1:F30").Sort
......

But what happen is I may have different ranges of lists! Sometimes, the list
may be shorten then the previous one but sometimes may be longer then. So, I
tried to replace the fixed range with a Range Object variable; but it doesn't
work and the system generated a debug!!!

How actually to solve this problem????
Please advise........

Thanking in advance!!