Sorting a Worksheet by the first 14 characters in Column A

  • Thread starter Thread starter PVANS
  • Start date Start date
P

PVANS

Good morning

Currently, each day I am required to sort and "clean" an Excel Worksheet.
In this worksheet, there is a list of accounts. However, in this unordered
list, are pairs of each account with three extra characters on the end. eg:
100001/1000001
100001/1000002
100001/1000004
100002/2000002
100001/1000001DMA
100002/2000002DMA

I would like to create a macro that checks the first 14 characters from the
left of the values in Column A, and groups each pair together as well as
leaving a line between each set.

Please could someone provide me with some advice for this issue? I really
would appreciate it.

Regards,
 
Option Explicit
Sub Main()
Dim lastrow As Long
Dim rw As Long
With Range("A:A")
.Sort .Range("A1")
lastrow = .Range("A1").End(xlDown).Row
End With
For rw = lastrow To 2 Step -1
If Left(Cells(rw, 1).Value, 14) <> Left(Cells(rw - 1, 1).Value, 14) Then
Rows(rw).Insert
End If
Next
End Sub
 
Back
Top