Unable to capture all the rows

[Michael1974]

Hello,

Please see the attached file. After running the Macro, I am unable to get all the rows from the initial data set. Can someone tell me why and how to fix this?

Thanks

[mperrah]

Its hard to see where the code fails.
There are several places where it puts text in a cell then puts a formula in the same cell.

It appears to take the data and parse it out into separate columns,
then copies to Column N
Filters for "Last" or "Final" then copies the results to sheet 3 and renames the sheet.

There may be an issue in the filter.

Are you wanting to show the 6000 original data rows?
The code shows results filtered for the 144 rows with "Last" or "Final"

This appears to have been made using the macro recorder.
There are many areas that can be streamlined, but how far off are the results?

[SamT]

Please see the attached file. After running the Macro, I am unable to get all the rows from the initial data set.
I have 606 rows of data in Sheet1 before and after runnig the macro

All that I have done to the code is to take out the artifacts of the Macro Recorder.


Sub Component_Task_Dec()
'
' Component_Task_Dec Macro

Sheets("Sheet1").Activate
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(34, 1), Array(44, 1), Array(57, 1), _
Array(65, 1), Array(73, 1), Array(83, 1), Array(102, 1)), TrailingMinusNumbers:=True

Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:I").Columns.AutoFit
Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


Range("B1") = "Serial Number"
Range("C1") = "Description"
Range("D1") = "Task Type"
Range("E1") = "Date"
Range("F1") = "TSN"
Range("G1") = "TSO"
Range("H1") = "NHA Number"
Range("I1") = "Activity"
Range("J1") = "Comments"
Columns("A:J").Columns.AutoFit

Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1") = "Final"
Range("B1") = "Rank"


Range("A2").FormulaR1C1 = "=IF(R[1]C[2]<>"""",""Last"","""")"
Range("B2").FormulaR1C1 = "=IF(OR(R[-1]C[-1]=""Last"",R[-1]C[-1]=""Final""),1,R[-1]C+1)"
Range("A2:B2").AutoFill Destination:=Range("A2:B6000"), Type:=xlFillDefault

Range("A1:K1").Copy Range("N1")

Range("N2").FormulaR1C1 = "=RC[-13]"
Range("O2").FormulaR1C1 = "=RC[-13]"
Range("P2").FormulaR1C1 = "=IF(RC[-1]=1,RC[-13],R[-1]C)"
Range("Q2").FormulaR1C1 = "=IF(RC[-2]=1,RC[-13],R[-1]C)"
Range("R2").FormulaR1C1 = "=IF(RC[-3]=1,RC[-13],R[-1]C)"
Range("S2").FormulaR1C1 = "=IF(RC[-4]=1,RC[-13],R[-1]C)"
Range("T2").FormulaR1C1 = "=IF(RC[-5]=1,RC[-13],R[-1]C)"
Range("U2").FormulaR1C1 = "=IF(RC[-6]=1,RC[-13],R[-1]C)"
Range("V2").FormulaR1C1 = "=IF(RC[-7]=1,RC[-13],R[-1]C)"
Range("W2").FormulaR1C1 = "=IF(RC[-8]=1,RC[-13],R[-1]C)"
Range("X2").FormulaR1C1 = "=IF(RC[-9]=1,RC[-13],R[-1]C&"" ""&RC[-13])"
Range("N2:X2").AutoFill Destination:=Range("N2:X6000"), Type:=xlFillDefault

Range("N1").AutoFilter
ActiveSheet.Range("$N$1:$X$6000").AutoFilter Field:=1, Criteria1:="<>"
Columns("N:X").Columns.AutoFit


Columns("P:X").Copy Sheets("sheet3").Range("A1")
Sheets("Sheet3").Name = "Consolidated Data"
With Sheets("Consolidated Data")
.Range("A:J").Columns.AutoFit
'.ListObjects.Add(xlSrcRange, Range("$A$1:$I$65090"), , xlYes).Name = "Table1"
'.ListObjects("Table1").TableStyle = "TableStyleLight9"
End With

End Sub