Automatically sorting columns simultaneously and in alphabetical order

[Moonbeam111]

Hey everyone. Can a VBA code do what I'm hoping in can do here. It's pretty simple to understand but difficult to implement. Say I have a column A and B.

Say I have this list in Column A's first 4 cells
James
Robert
John
Michael

And this list in column B first 4 cells
Michelle
Paul
Lauren
Mike

How would I get a vba code to automatically alphabetize both columns. I know how to sort a single column at a time. But if I wanted to sort both columns starting with column A and ending with column B (even having to move cells from column A to B or vice verca, is that doable?

For example the end result would look like this:
Column A
James
John
Lauren
Michael


Column B
Michelle
Mike
Paul
Robert

 

[DanteAmor]

Try this

Sub sortingcolumns()
  Dim lr As Long
  lr = Range("A" & Rows.Count).End(3).Row
  
  Range("A" & lr + 1).Resize(lr).Value = Range("B1:B" & lr).Value
  Range("A" & lr * 2).Sort Range("A1"), xlAscending
  
  Range("B1:B" & lr).Value = Range("A" & lr + 1).Resize(lr).Value
  Range("A" & lr + 1).Resize(lr, 2).Value = ""
End Sub

 

[Peter_SSs]

Do you need to use vba and have the result in the same cells? Just asking because if you have the relevant functions it can be done with a formula in a single cell elsewhere.

Moonbeam111.xlsm
	A	B	C	D	E
1	James	Michelle		James	Michelle
2	Robert	Paul		John	Mike
3	John	Lauren		Lauren	Paul
4	Michael	Mike		Michael	Robert
Sort

Cell Formulas
Range	Formula
D1:E4	D1	=WRAPCOLS(SORT(TOCOL(A1:B4)),ROWS(A1:B4))
Dynamic array formulas.

 

[Moonbeam111]

Do you need to use vba and have the result in the same cells? Just asking because if you have the relevant functions it can be done with a formula in a single cell elsewhere.

Well it doesn't matter if it's with vba but I do prefer if it could be done with the same cells.

Try this

Sub sortingcolumns()
  Dim lr As Long
  lr = Range("A" & Rows.Count).End(3).Row
 
  Range("A" & lr + 1).Resize(lr).Value = Range("B1:B" & lr).Value
  Range("A" & lr * 2).Sort Range("A1"), xlAscending
 
  Range("B1:B" & lr).Value = Range("A" & lr + 1).Resize(lr).Value
  Range("A" & lr + 1).Resize(lr, 2).Value = ""
End Sub

This code worked great for the example provided but how would I modify it for only columns S and T. Both columns have names that stretch to the 30th cell starting from the 1st cell in each column.

I tried to modify it but got an application or object defined error.

 

[Peter_SSs]

Both columns have names that stretch to the 30th cell starting from the 1st cell in each column.

Are you saying that ..

1. S1:T30 is a fixed range, and
2. All 60 cells have values in them?

 

[Moonbeam111]

Are you saying that ..

1. S1:T30 is a fixed range, and
2. All 60 cells have values in them?

1. No I may need to add more columns with names in them.
2. No some of the cells are empty but will eventually get populated as I add more.

For now, it would just suffice to be able to add a name in column S. Get to the 30th name in column S and then start on Column T. Once I enter values in column T I was hoping to be able to call a macro that will automatically sort it into column S in alphabetical order. As demonstrated in the opening post.

I'm only running excel 2010 so I'm not sure I can run your formula.

 

[DanteAmor]

how would I modify it for only columns S and T

Try:

Sub sortingcolumns()
  Dim lr As Long
  lr = Range("S" & Rows.Count).End(3).Row
  
  Range("S" & lr + 1).Resize(lr).Value = Range("T1:T" & lr).Value
  Range("S1:S" & lr * 2).Sort Range("S1"), xlAscending
  
  Range("T1:T" & lr).Value = Range("S" & lr + 1).Resize(lr).Value
  Range("S" & lr + 1).Resize(lr, 2).Value = ""
End Sub

 

[DanteAmor]

Edit:

Sub sortingcolumns()
  Dim lr As Long
  lr = Range("S" & Rows.Count).End(3).Row
  
  Range("S" & lr + 1).Resize(lr).Value = Range("T1:T" & lr).Value
  Range("S1:S" & lr * 2).Sort Range("S1"), xlAscending
  
  Range("T1:T" & lr).Value = Range("S" & lr + 1).Resize(lr).Value
  Range("S" & lr + 1).Resize(lr, 2).Value = ""
End Sub