Dim rng As Range
Dim what(50) As String
Application.ScreenUpdating = False
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(22, 1), Array(30, 1), Array(35, 1), _
Array(66, 1), Array(73, 1), Array(77, 1), Array(87, 1), Array(92, 1), Array(101, 1), Array( _
109, 1)), TrailingMinusNumbers:=True
Columns("A:A").EntireColumn.AutoFit
Range("C:C,D:D,E:E,F:F,H:H,I:I,L:L").Select
Range("L1").Activate
Selection.Delete Shift:=xlToLeft
Range("1:7,65:73,131:139,197:205,263:271,329:337,395:403,461:469,527:535,593:601,659:667,725:733,791:799,857:865,923:931,989:997").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Range("A1:E2000").Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range _
("B1"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
ActiveCell.SpecialCells(xlLastCell).Select
Range("A1").Select
what(1) = "A-A-0"
what(2) = "A-A-1"
what(3) = "A-A-2"
what(4) = "A-B-0"
what(5) = "A-B-1"
what(6) = "A-B-2"
what(7) = "A-C-0"
what(8) = "A-C-1"
what(9) = "A-C-2"
what(10) = "A-D-0"
what(11) = "A-D-1"
what(12) = "A-D-2"
what(13) = "A-E-0"
what(14) = "A-E-1"
what(15) = "A-E-2"
what(16) = "A-F-0"
what(17) = "A-F-1"
what(18) = "A-F-2"
what(19) = "B-A-0"
what(20) = "B-A-1"
what(21) = "B-A-2"
what(22) = "B-B-0"
what(23) = "B-B-1"
what(24) = "B-B-2"
what(25) = "B-C-0"
what(26) = "B-C-1"
what(27) = "B-C-2"
what(28) = "B-D-0"
what(29) = "B-D-1"
what(30) = "B-D-2"
what(31) = "B-E-0"
what(32) = "B-E-1"
what(33) = "B-E-2"
what(34) = "B-F-0"
what(35) = "B-F-1"
what(36) = "B-F-2"
what(37) = "C-A-0"
what(38) = "C-A-1"
what(39) = "C-A-2"
what(40) = "C-B-0"
what(41) = "C-B-1"
what(42) = "C-B-2"
what(43) = "C-C-0"
what(44) = "C-C-1"
what(45) = "C-C-2"
what(46) = "C-D-0"
what(47) = "C-D-1"
what(48) = "C-D-2"
what(49) = "C-E-0"
what(50) = "C-E-1"
For i = 1 To 50
Do
Set rng = ActiveSheet.UsedRange.Find(what(i))
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
Next i
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "LOCATION"
Range("B1").Select
ActiveCell.FormulaR1C1 = "HAWB"
Range("C1").Select
ActiveCell.FormulaR1C1 = "DEST"
Range("D1").Select
ActiveCell.FormulaR1C1 = "PCS"
Range("E1").Select
ActiveCell.FormulaR1C1 = "STATUS"
Range("A1:E1").Select
Selection.Font.Bold = True
Application.ScreenUpdating = True
The full code was too long, had to delete some but changed the macro to make it work if you try to run it!
Bookmarks