Excel VBA - Sort AlphaNumeric Data

Question

I have a table in Excel with 2 columns to the right something like the follwing:

Group       DateValue

1A	46754
2A	41213
2B
3A	41060
3B
4A	41060
5A	41061
5B

I want to sort the table by the Datevalue Column first and then the Group Column. I also need to keep the groups together. So it would look somehting like:

3A	41060
3B
4A	41060
5A	41061
5B
2A	41213
2B
1A	46754

Would anyone have any suggestions on how this can be done in VBA? Thanks in advace!

Answer 1

Hi,

try this code

data on Sheet1 in columns A, B (in row 1 are headings),

export data in a new Sheet

Sub mySort()

Dim ws As Worksheet, newWS As Worksheet

Dim r As Long

Set ws = Sheets("Sheet1") '<< change name

r = ws.Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False

Set newWS = Sheets.Add

ws.Range("A1:B" & r).Copy Destination:=newWS.Range("A1")

For i = 2 To r - 1

If Left(newWS.Cells(i, 1), 1) = Left(newWS.Cells(i + 1, 1), 1) Then

newWS.Cells(i + 1, 2) = ws.Cells(i, 2)

newWS.Cells(i + 1, 2).Interior.ColorIndex = 6

End If

Next

newWS.Range("A2:B" & r).Sort key1:=newWS.Range("B2"), Header:=xlNo

For Each rr In newWS.Range("B2:B" & r)

If rr.Interior.ColorIndex = 6 Then

rr.Value = ""

rr.Interior.ColorIndex = 0

End If

Next

Application.ScreenUpdating = True

End Sub