CODEWITHSUNDEEP
excelvba
Cla Rosie
Cla Rosie

Reputation: 365

Sorting List but Retaining Individual Cells' Formatting

I'm trying to sort a list of cells alphabetically. Each cell has a different pre-set format and I don't want to lose this post sort. I would like the macro to register the format of each cell pre-sort and then re-apply it post sort.

In this particular example, I have some cells that have strikethrough font but finding it super difficult to find a way to make Excel 'memorise' this pre-sort.

There are a lot of questions on this topic, none of which actually resolves the issue via VBA. Some suggest conditional formatting each cell individually, whereas others overlay a pre-set format. I've tried this 'old format overlay' method (see below) but need to find a way to make the memorising of the format specific to each cell...

Do you have any ideas?

Latest attempt (old format overlay):

Dim StrikeThrough As Variant
Dim i, j As Long
            
        ReDim StrikeThrough(1 To Y.Worksheets("Test").Range("FormattedCells").Rows.Count, 1 To 
        Y.Worksheets("Test").Range("FormattedCells").Columns.Count)
            For j = 1 To UBound(StrikeThrough, 2)
                For i = 1 To UBound(StrikeThrough, 1)
                    StrikeThrough(i, j) = Y.Worksheets("Test").Range("FormattedCells").Cells(i, j).Font.StrikeThrough
                Next
            Next

Upvotes: 1

Views: 838

Answers (1)

Chronocidal
Chronocidal

Reputation: 8056

Thank you for explaining your end-goal ("sort the values, but leave the formatting where it was"), so that we can avoid the pitfalls of an XY Problem.

Rather than Sorting the cells with Range.Sort, Sort the values, by converting them into an Array, sorting the Array, and then putting the Array back into the Range. This way, the Cell Formatting will remain exactly where it was.

If you have Office365, then it's even easier; with the introduction of the SORT function into Excel (accessible in VBA via WorksheetFunction.Sort), you can do all 3 steps in 1 line:

Dim Target As Range
Set Target = Sheet1.Range("A2:C12")

Target.Value = WorksheetFunction.Sort(Target.Value, 1, 1) '1st Column, Ascending
'For 1st Column, Descending, use (Target.Value, 1, -1)
'For 2nd Column, Ascending, use (Target.Value, 2, 1)

If you are using an older version of Excel, then you will need to write your own function to sort the array (e.g. a Bubble Sort?), and use that in place of WorksheetFunction.Sort

Upvotes: 2

Related Questions