I have to make small list out of a large data set that has new values added to it weekly. The macros are to delete the unnecessary data and format whats left depending on the list requirements.

This is the code I have to select the data after the unneeded columns are deleted and format it to a table.

‘Fromat to grey scale table and sort by start date (E column) last to first

Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$4:$I$166"), , xlYes).Name = _
    "Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium4"

ActiveWorkbook.Worksheets("Crew Payroll Bible").ListObjects("Table1").Sort. _
    SortFields.Clear
ActiveWorkbook.Worksheets("Crew Payroll Bible").ListObjects("Table1").Sort. _
    SortFields.Add2 Key:=Range("Table1[[#All],[Start Date]]"), SortOn:= _
    xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Crew Payroll Bible").ListObjects("Table1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Worked great the first time. But now the vertical selection from A4 down stops at line 166 every time leaving the rest of the data unselected. There are no empty cells and I’ve tried sorting the data file first and again it stops on line 166. I’m baffled.

Checked for empty cells, tried resorting the list.