Sorting data in a macro messes up formatting

[Moosles]

As part of a macro to prepare client ready files, a tab with various tables of data of varying sizes gets filtered to show empty rows and these rows are then deleted. This is done using the following code:

Range("A2:A" & Range("A" & Rows.Count).End(xlUp).row).AutoFilter Field:=1, Criteria1:="Delete"
Range("A2").Activate
Range(ActiveCell, ActiveCell.End(xlDown)).Delete Shift:=xlUp

Column A contains formulas to see if the contents of each row is empty and returns "Delete" if it is, and "" if it isn't. The issue is that as the workbook that this output is taken from has gotten larger and slower over time, the option of deleting the rows has slowed the macro down considerably, and so I have been looking to apply an alternative that sorts the data on column A alphabetically, locates the first row to be cleared and clears everything below it, leaving only the desired data:

    StartCell = Range("A2").Address
    LastRow = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    LastColumn = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column
    SortSheet = ActiveSheet.name
    
    ActiveWorkbook.Worksheets(SortSheet).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(SortSheet).Sort.SortFields.Add Key:=Range(StartCell), SortOn:=xlSortOnValues, Order:=xlAscending
    With ActiveWorkbook.Worksheets(SortSheet).Sort
        .SetRange Range(StartCell, Cells(LastRow, LastColumn).Address)
        .header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Range(Cells(1, LastColumn + 1).Address).Formula = "=IFERROR(MATCH(""Delete"",A2:A" & LastRow & ",0)+1," & LastRow + 1 & ")"
    Range("A" & Range(Cells(1, LastColumn + 1).Address).value & ":" & Cells(LastRow, LastColumn).Address).Clear
    Range(Cells(1, LastColumn + 1).Address).Clear

This works really nicely in sorting the data into 2 sections, Data to Keep at the top and Data to Clear at the bottom. The issue is that the formatting gets messed up when the data is sorted. There is no conditional formatting on this sheet, and the tables of data that are included in this output will be of inconsistent sizes so I wouldn't be able to use any conditional formatting when this is sorting - the tables have ~300 rows each to begin with so I wouldn't want to have to apply any manual conditional formatting to this anyway!

Any idea why the formatting would get messed up in this way, and what can I do to avoid it?

 

[Akuini]

The issue is that the formatting gets messed up when the data is sorted.

If after sorting, and then deleting, you can sort the data back to its original order, would that preserve the formatting?

 

[Alex Blakenburg]

Any idea why the formatting would get messed up in this way, and what can I do to avoid it?

Unfortunately some formatting stays with the data but some stay with the actual cell address and hence are no longer applied to the data you want it to be applied to.
(@Akuini - sorting it back after a delete won't match the data with the original row, so the formating won't line up with the data)

The ones that stay with the cell address and become an issue are:
• Borders
• Custom Row heights
Cell colour and Font are normally fine and move with the data, row height that have auto fit to the cell content eg by setting the font to 14 are dependant on the data an will also move with the data.

@Peter_SSs has some code here which identifies the data to keep and copies the entire row to a new worksheet preserving the formatting.
It also doesn't use a formula to identify what to keep/delete so it is much faster.
Delete 100000 rows based on matching the value for first row with last row

You haven't mentioned which formatting is causing you grief is it only Row Height or do you also have borders ?