VB Code to Sort with reference to active cell

awagdarikar

Board Regular
Joined
Jun 20, 2008
Messages
115
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
My Code is <o:p></o:p>
<o:p> </o:p>
Sub SORT_ACTIVECELL()<o:p></o:p>
<o:p> </o:p>
Dim strCol As String<o:p></o:p>
<o:p></o:p>
strCol = <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:place w:st="on"><st1:City w:st="on">Split</st1:City></st1:place>(Columns(ActiveCell.Column).Address(, False), ":")(0)<o:p></o:p>
<o:p> </o:p>
Range("A2:E17").Sort Key1:=Range("strCol" & 2), _<o:p></o:p>
Order1:=xlAscending, Header:=xlGuess, _<o:p></o:p>
OrderCustom:=1, MatchCase:=False, _<o:p></o:p>
Orientation:=xlTopToBottom<o:p></o:p>
<o:p></o:p>
End Sub<o:p></o:p>
<o:p> </o:p>
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:p></o:p>
:rolleyes:
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

ilyaskazi_01

New Member
Joined
Jul 21, 2008
Messages
36
Try this...

Code:
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

Board Regular
Joined
Jun 20, 2008
Messages
115
Try this...

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top