Sorting data in a macro messes up formatting

Moosles

New Member
Joined
Apr 1, 2021
Messages
23
Office Version
  1. 2019
Platform
  1. Windows
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:

VBA 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:

VBA Code:
    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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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?
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,223,882
Messages
6,175,166
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top