macro to alphabetize selected cells
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: macro to alphabetize selected cells

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I've tried recording a macro to see if I can get this without asking, I can get it to do some of the cells but not all of them. This is what I'm working on. I have a spreadsheet with employee names in merged Cells(A11:B22)(A11:B11) are merged and contain a name, then in column and cell (C11) I have a salary for that name. I've tried to select all cells (A11:C22) so that when they are alphabetized the salary will stay with name it goes with, but I get error saying that merged cells must be the same size. How can I alphabetize all the names along with their salary. I am also wanting to allow for the input of new names then re-alphabetizing for the new names as well. So if I have names A through W already alphabetized and I will be using a userform to input the new name and salary(which I know the code to use to insert the new row) but then if the name is C after it is inserted then all the names will need to be alphabetized for the new entry. Any ideas?
    I appreciate the help from everyone at Mr. Excel.

    viper

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Viper
    I recommend you unmerge the cells if possible and separate the first and last names. (Use Data/Text to Columns) Create a UserForm with 3 text boxes and 2 command buttons. The following code will put the last name in column A starting in row 3, the first name in column B (row 3)and the salary in column C (also row 3). You must have headings at least in rows 1 and 2 for this to work.

    Public Sub Show()
    UserForm1.Show

    End Sub

    Private Sub CommandButton1_Click()
    Range("B1").Select
    Range("B1").End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = TextBox1
    Range("A1").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = TextBox2
    Range("C1").Select
    Range("C1").End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = TextBox3
    End Sub

    To unload the user form use this.

    Private Sub CommandButton2_Click()
    Unload Me
    End Sub

    To sort the selection down to row 40 use this.

    Sub Macro1()
    ' Macro1 Macro
    Application.ScreenUpdating = False
    Range("A3:C40").Select
    Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("F1").Select
    End Sub

    Good luck

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com