H
Hari
Hi,
I have data in Column B thru V.
Row no 1 is header row.
Data extends till lets say row number Z where Z keeps on changing depending
on the amount of data. For calculating the value of Z, I use the no of
entries in column F as the basis ( as it could be possible that cell B59, C
59 has no data but F59 will have data) This Z is calculated within the VB
code and designated as the variable "rowcount".
Now comes the real ( and difficult part)...
I want to sort the above data.
I recorded the macro as per my sorting specifications and got the following
code:
Rows("2:" & rowcount - 1).Select
Selection.Sort Key1:=Range("U2"), Order1:=xlDescending,
Key2:=Range("M2") _
, Order2:=xlDescending, Key3:=Range("L2"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Selection.Sort Key1:=Range("V2"), Order1:=xlDescending,
Key2:=Range("U2") _
, Order2:=xlDescending, Key3:=Range("M2"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Im sorting 2 times because excel's normal application has "Sort By" feature
only for 3 columns at a time. But in my case I have to sort data depending
on data in 4 columns hence did sorting 2 times. Here my sorting is done with
Column V having the highest priority then U then M and lastly L.
Basically I have dates in Column V,M,U and L.
Problem is there are occasions when dates in column V will not be greater
than date in the other 3 columns. For those variable times I want to
determine the row which has the maximum of the dates in the range of column
L2 to V "rowcount" ( only column L, M, U and V to be used for determining
maximum date and rowcount is the maximum number or rows to which the data
extends) then use this information and cut this particular row and move it
to row number 2 which is the top row and similarly Now I want to find that
row which has the maximum of dates in the range L3 to V "rowcount" and then
cut that whole row and move it to insert it in place of row number 3 and I
want to keep doing this till the last row which is rowcount.
Please guide me for the same.
Regards,
Hari
India
I have data in Column B thru V.
Row no 1 is header row.
Data extends till lets say row number Z where Z keeps on changing depending
on the amount of data. For calculating the value of Z, I use the no of
entries in column F as the basis ( as it could be possible that cell B59, C
59 has no data but F59 will have data) This Z is calculated within the VB
code and designated as the variable "rowcount".
Now comes the real ( and difficult part)...
I want to sort the above data.
I recorded the macro as per my sorting specifications and got the following
code:
Rows("2:" & rowcount - 1).Select
Selection.Sort Key1:=Range("U2"), Order1:=xlDescending,
Key2:=Range("M2") _
, Order2:=xlDescending, Key3:=Range("L2"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Selection.Sort Key1:=Range("V2"), Order1:=xlDescending,
Key2:=Range("U2") _
, Order2:=xlDescending, Key3:=Range("M2"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Im sorting 2 times because excel's normal application has "Sort By" feature
only for 3 columns at a time. But in my case I have to sort data depending
on data in 4 columns hence did sorting 2 times. Here my sorting is done with
Column V having the highest priority then U then M and lastly L.
Basically I have dates in Column V,M,U and L.
Problem is there are occasions when dates in column V will not be greater
than date in the other 3 columns. For those variable times I want to
determine the row which has the maximum of the dates in the range of column
L2 to V "rowcount" ( only column L, M, U and V to be used for determining
maximum date and rowcount is the maximum number or rows to which the data
extends) then use this information and cut this particular row and move it
to row number 2 which is the top row and similarly Now I want to find that
row which has the maximum of dates in the range L3 to V "rowcount" and then
cut that whole row and move it to insert it in place of row number 3 and I
want to keep doing this till the last row which is rowcount.
Please guide me for the same.
Regards,
Hari
India