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

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
  • 2
  • 2
LVL 50

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 50

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

856 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