Try this
Sub CleanUp()
For N = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(N, 1) = "" Then
Rows(N).Delete
ElseIf Cells(N, 1) = 0 Then
Rows(N).Delete
ElseIf IsNumeric(Cells(N, 1)) = False Then
Rows(N).Delete
End If
Next N
For N = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Left(Cells(N, 1), 2) = "27" Then
Rows(N + 1).Insert
Cells(N + 1, 1) = Val(Mid(Cells(N, 1), 10))
Cells(N, 1) = Val(Left(Cells(N, 1), 9))
End If
Next N
Columns(1).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
Open up the VBA editor by hitting ALT F11
Insert a new module by hitting Insert - Module
Paste the macro into the empty sheet
Hit ALT F11 to get back to the worksheet.
Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.
Bookmarks