VB Code to Sort with reference to active cell

[awagdarikar]

Hi,

My data is in range A2:E17. I want a VB Code to sort data based on the cell address where cursor is placed (active cell) in Row 2. Suppose my active cell address is "A2", then the code should sort my data range A2:E17 with reference to "A2". I have tried something which is as below but not giving me Run Time Error 1004.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
My Code is <o></o>
<o> </o>
Sub SORT_ACTIVECELL()<o></o>
<o> </o>
Dim strCol As String<o></o>
<o></o>
strCol = <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:City w:st="on">Split</st1:City></st1lace>(Columns(ActiveCell.Column).Address(, False), ":")(0)<o></o>
<o> </o>
Range("A2:E17").Sort Key1:=Range("strCol" & 2), _<o></o>
Order1:=xlAscending, Header:=xlGuess, _<o></o>
OrderCustom:=1, MatchCase:=False, _<o></o>
Orientation:=xlTopToBottom<o></o>
<o></o>
End Sub<o></o>
<o> </o>
I know the error is in the range definition Range("strCol" & 2) because if I put a cell address like A2, my code executes properly. VB does not accept activecell.value as range.

Some body please help me out,

awagdarikar<o></o>

 

[ilyaskazi_01]

Try this...

Sub Sort_MyRange()

Range(ActiveCell.Address(False, False) & ":A17").Sort Key1:=Range(ActiveCell.Address(False, False)), _
    Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

..

 

[awagdarikar]

Try this...

Sub Sort_MyRange()
 
Range(ActiveCell.Address(False, False) & ":A17").Sort Key1:=Range(ActiveCell.Address(False, False)), _
    Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

..

Hi ilyaskazi_01,

Thanks for your reply but your code sorts only the range which is lying between the cell A17:Activecell. My requirement is different. I need sorting of the entire range A17:E17 each time the macro runs. The active cell should have no effect on the Data Range.

I have a dead line to follow please help me out,

awagdarikar