Keep SpecialCell(XlCellTypeComments) on top while sorting data

[ImranBhatti]

Hi

I have a worksheet with comments in A column (some cells contains comments and some don't)
their are 2 adjacent columns filled with data (B and C)

I want to sort my dataset on the basis of A column but the cells containing the comments should remain on top and the rest of the cells should be sorted beneath them (the comments cells should remain on top but should be sorted at their own place)

Help would be greatly appreciated

Best Regards
Imran Bhatti

[MarvinP]

Hi Imran,

I think you are looking for the Review Tab and click on "Show All Comments". Then when you sort column A the comment boxes stay where they are and only the lines to the cells move.

https://www.howtogeek.com/218633/how...ines-in-excel/

[ImranBhatti]

Hi sir MarvinP
Thanks for the review

I have attached a sample showing "Before sorting" and "After sorting"
hope that helps to understand the requirement easily.

[vichopalacios]

hi @ImranBhatti
Here you can find a simple piece of code to do your requirement.
It's using column D as auxiliar
I hope it's helpfull for you

Sub SortComments()
Dim myWs As Worksheet
Dim myRange As Range
Dim LastRow As Long
Dim i As Long

Set myWs = Worksheets("Sheet2")
With myWs
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set myRange = .Range("A1:D" & LastRow)
    For i = 1 To LastRow
        If .Cells(i, 1).Comment Is Nothing Then
            .Cells(i, 4).Value = 1
        Else
            .Cells(i, 4).Value = 0
        End If
    Next i
End With
myRange.Select
With myWs.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("A1:A" & LastRow), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange myRange
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
With myWs.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("D1:D" & LastRow), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange myRange
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

[ImranBhatti]

Doing exactly what I want.Any chance we can avoid the use of helper column OR can we assign a column that is not adjacent to the datagrid I mean can I use XFD column as helper?

[vichopalacios]

As far as I know, sorting is not working on discontinuos ranges.
I think the simplest way would be inserting a column before the procedure ( just after the Dim instructions), and deleting it after.

[ImranBhatti]

I think the simplest way would be inserting a column before the procedure ( just after the Dim instructions), and deleting it after.

Clever move.Thanks