VBA WorksheetChange Event To sort Data based on a specific column

Posted on 2015-01-15
Last Modified: 2015-01-15

please see attached file.
i need help with VBA on worksheet changeevent that whenever any new data or any change made on sheet1, the VBA auto sorts in ascending order one column that contains the header label "SP Name"

Question by:Flora
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 51

Expert Comment

ID: 40551150

pls try

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Range(Range("A1"), Range("K" & Cells.Rows.Count).End(xlUp)).Sort _
            key1:=Range("D1"), order1:=xlAscending, Header:=xlYes
End Sub

Open in new window


Author Comment

ID: 40551165
Thanks very much Rgonzo1971

if you are away from your machine, i can wait, the above codes works, but there are two issues here.

A) my data varies time to time, so my end of data to the left is not column K, so it could expand to further columns.
is there any possibility that to use somthign that works dynamic instead of fixed column K?

B) if the sort column could also be set dynamically. for example  trigger sort only in a column where its header input name is "SP Name"     so for example. if my next data varies and the SP Name is in Column C then the above code will not work unless i manually change it key1:=Range("C1"),   so would it be possible to put a seach on key1:= search"Sp Name", in row 1 ? somthing like this?
LVL 51

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 40551191
Then try

Private Sub Worksheet_Change(ByVal Target As Range)
    lngCol = Range("1:1").Find("SP Name").Column
    ActiveSheet.Range(Range("A1"), Range("K" & Cells.Rows.Count).End(xlUp)).Sort _
            key1:=Cells(1, lngCol), order1:=xlAscending, Header:=xlYes

End Sub

Open in new window


Author Closing Comment

ID: 40551238
you are my hero :-)

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel: Macro to Contain Sorting for a Pivot Table 5 33
drag and drop a row in excel 2010 5 37
Lookup range formula 7 22
count values within multiple bands 7 29
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

731 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question