Search code examples
excelexcel-2007vba

Abbreviations management

I need to deal with a great number of abbreviations, so I would like to create a spreadsheet where all of them are automatically ordered as I enter each in a new cell. Also, the whole row corresponding to such cell must be moved along when ordered alphabetically.

I am just supposing this is the best way to proceed, so any ideas will be welcomed.

Solution

  • Say we have something like:

    enter image description here

    Out editing procedure says to enter the full text in column B and then the abbreviation next to it in column A. First place the following sub in a standard module:

    Sub Macro1()
   Dim N As Long
   N = Cells(Rows.Count, "A").End(xlUp).Row
   Columns("A:B").Select
   ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A" & N), _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   With ActiveWorkbook.Worksheets("Sheet1").Sort
      .SetRange Range("A1:B" & N)
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With
End Sub

    Then place this event macro in the worksheet code area:

    Private Sub Worksheet_Change(ByVal Target As Range)
   Dim A As Range
   Set A = Range("A2:A" & Rows.Count)
   If Intersect(Target, A) Is Nothing Then Exit Sub
   If Target.Offset(0, 1) = "" Then Exit Sub

   Application.EnableEvents = False
      Call Macro1
   Application.EnableEvents = True
End Sub

    Once macros are enabled, changes to cells in column A will cause the table to re-sort.