Sorting Excel Data using VB

• • Reply

  12 years ago

  by udaynti

  udaynti patel

  , Nepal

  Joined 13 years ago

  Dear Community

  I have a dataset on an Excel spreadsheet (currently within the cell range A6 to I54). I am using the VB SortRange procedure driven by a macro button , shown below (Code 1), to sort data first using Key1 input (in this case the contents of Column I starting at Row 6), and then by Key 2 input (in this case the contents of Column A starting at Row 6). This is all working perfectly and I have no issue with this. As the dataset is going to have many lines added to it, the end of the range (in Column I) is going to change frequently and not remain constant at Row 54. What I need to do is get the code to determine the end of the range automatically, rather than me or anyone else to have to define it explicitly each time additional rows are added. I have tried 2 other versions of sort procedure to deal with this problem (see Code 2 and Code 3), but I keep getting the error message 'Run Time Error '1004' - The Sort reference is not valid etc. Can anyone let me know how to get VB to carry out this additional operation? I know this is quite straightforward, but it isn’t working for me yet.

  In addition, I would like this sorting procedure to be driven automatically using a cell 'event' trigger (e.g. 'change event') to run this macro dynamically, rather than having to manually press a macro button. Can anyone help with this please?

  Any help would be most gratefully received.

  Best wishes Eliot

  Code 1 

  Sub SortRange1() Worksheets("Schedule").Range("A6:I54").Sort _

  Key1:=Worksheets("Schedule").Range("I6"), _

  Key2:=Worksheets("Schedule").Range("A6")

  End Sub

   

  Code 2

  Sub SortRange1()

     Worksheets("Schedule").Range("A6").Sort _

          Key1:=Worksheets("Schedule").Range("I6"), _

          Key2:=Worksheets("Schedule").Range("A6")

  End Sub

   

  Code 3

  Sub SortRange1()

     Worksheets("Schedule").Range("A6").Sort _

          Key1:=Worksheets("Schedule").Columns("I"), _

          Key2:=Worksheets("Schedule").Columns("A"), _

          Header:=xlGuess

  End Sub

  Report abuse