Can you sort SpecialCells or other noncontiguous cells

[TheAntiGates]

After
Selection.SpecialCells(xlCellTypeComments).Select
can I in somehow selection.sort this, ideally by cell addresses?

I understand that this is an usual request, but the results of the following snippet are not consistent (at least following saving and reopening a workbook). One time I'll get

sheet1 cell A2:{thiscomment}(invisible)
sheet1 cell B3:{another comment}(invisible)

and another time it's
sheet1 cell B3:{another comment}(invisible)
sheet1 cell A2:{thiscomment}(invisible)

            For Each c In Selection
                Print #1, wksht.Name & " cell " & Mid(c.Address, 2, InStr(2, c.Address, "$") - 2) & c.Row & ":{" _
                    & c.Comment.Text & "}" & IIf(Not c.Comment.Visible, "(invisible)", "")
            Next c

[snb]

For j=1 to Selection.cells.count
  with selection.cells(j)
    Print #1, wksht.Name & " cell " & Mid(.Address, 2, InStr(2, .Address, "$") - 2) & .Row & ":{" & .Comment.Text & "}" & IIf(Not .Comment.Visible, "(invisible)", "")
  end with
Next

[TheAntiGates]

Can't test this until tomorrow but that looks sweeeet. I'm sensing that you've determined that Selection is in cell order, but "for each" dances around, sequentially speaking, so you're avoiding For Each. If so that may clear up some long standing mysteries perhaps attributable to For Each not working sequentially.

It's odd that "for each ws in worksheets" works in perfect sequential order every time I use it. Either I've been incredibly lucky (thousands if times) or perhaps For Each behaves differently depending on context, sequentially speaking.

edit: earlier post s/b "unusual" rather than "usual" - doh.

[snb]

The specialcells facility creates 'areas' (adjacent cells), not separate cells. They may differ, dependent of what is being encountered; Besides I have the impression that the algorithm that creates the areas isn't producing them consistently in the way we would expect it from a user's perspective.

[TheAntiGates]

Aw, bad news, the j loop doesn't appear to work.

dim c as Range
For Each c In Selection
     Debug.Print c.Address

gave this
$G$2
$C$37
$D$39

but

For j = 1 To Selection.Cells.Count
        Debug.Print Selection.Cells(j).Address

gave
$G$2
$G$3
(then la Bomba since there's no .comment on G3). I'm really disappointed, since each selection region is only 1 cell I gather, so your code should have simply worked.

Maybe Cells(k+1) is strictly Cells(k).offset(0,1)? * So it didn't "walk through" the selection, it just offset from the first cell? (* Maybe not {0,1} when at the right edge of the spreadsheet)

Separate thought, I should mention that although For Each gave results in row order above, in my experience it could have just as easily given them out of order, leading to my question in the first place.

[snb]

if you want the results rowlike:

Sub M_snb()
    Set sn = Range("A1:F6").SpecialCells(2)
    
    For j = 1 To UsedRange.Rows.Count
      For Each cl In Intersect(sn, Rows(j))
         c00 = c00 & "_" & cl.Address
      Next
    Next
    
    MsgBox c00
End Sub

[TheAntiGates]

Ow, that's grotesque, to walk through the intersections with .UsedRange. At least it might beat the best I had as an alternative, to write everything to a temp worksheet; or to an array in memory and sort the array. Thanks still for coming up with something workable.

I'll leave the thread UNSOLVED in case someone knows internals more intimately regarding
- noncontiguous ranges' ordering, and accessibility in order
- whether For Each is random in access, or does it access sequentially (in which case the misordering is in what it is itemizing)
- Does For Each return sequentially only in some contexts (e.g. for each sht in sheets seems perfectly ordered)
- Except for Cells(...,Columns.Count), does Cells(k+1) mean Cells(k).offset(0,1)
(sure, for activesheet.cells; but how about range("XX").Cells ? )

By the way, I mentioned selection.Sort in the O.P. but that was just babbling.

Thanks for your smart work snb.

[Jan Karel Pieterse]

If the selection contains areas, this should do it:

Sub foo()
    Dim lct As Long
    Dim oArea As Range
    For Each oArea In Selection.Areas
        For lct = 1 To oArea.Cells.Count
            MsgBox oArea.Cells(lct).Value
            'Your comment code goes here
        Next
    Next
End Sub

[TheAntiGates]

Mr. Pieterse, I'm very honored that someone of your fame and talent visited my little thread. That is a great solution. All I was really hoping for was better consistency than For Each gave, but this even usually correctly orders selection elements by row and even within the same row, a bonus. I found an exception to that but it is probably attributable to MS's algorithm for assigning areas. For example this sequence came out:
N47
C56
C57
H56
H57
C59

Probably C56:C57 were assigned to one area and H56:H57 to another. Your code did what it was expected to do in that situation.

As to the "nagging questions" in my last post, feel free to share them with your circle of geniuses However I suspect that to get an "exact" (sorted) ordering of the selection I'll have to stick the elements in an array, or in a scratch worksheet, and sort.

Thank you very, very much for this solution.

[TheAntiGates]

Bad news. Not every element from
Selection.SpecialCells(xlCellTypeComments).Select
is in an area. Consequently the walk through the areas did not return everything in the selection

[snb]

and what about the result in:

Sub M_snb()
    Set sn = Range("A1:F6").SpecialCells(2)
    
    For j = 1 To Range("A1:F6").Rows.Count
      For Each cl In Intersect(sn, Range("A1:F6").Rows(j))
         c00 = c00 & "_" & cl.Address
      Next
    Next
    
    MsgBox c00
End Sub

[TheAntiGates]

All right, your suggestion is still cheaper than collecting and sorting all the Selection items so I used your code but with .UsedRange as you suggested earlier. Well done.

Below is code to save entire WB's comments into a file "foo" in CurDir. Note: 1. No error checking such as for opening the output file. 2. If there are comments in Chart sheets they're out of scope here.

Sub pm_Save_AllSheetCommentsAsTextFile()
    'Save all worksheets' comment info in a text file
    'Active sheet and selection are both affected
    Dim wksht As Worksheet, c As Range, j As Integer, bNoComments As Boolean
    
    Open "foo" For Output As #1   'open the file for output
    Print #1, "Comments in workbook " & ActiveWorkbook.Name
    For Each wksht In Worksheets
        wksht.Activate
        bNoComments = False
        On Error GoTo err2_Save_AllSheetCommentsAsTextFile
        Selection.SpecialCells(xlCellTypeComments).Select
        On Error GoTo 0
        If Not bNoComments Then
            For j = 1 To ActiveSheet.UsedRange.Rows.Count
                If Not Intersect(Selection, Rows(j)) Is Nothing Then
                    For Each c In Intersect(Selection, Rows(j))
                        Print #1, wksht.Name & " cell " & Mid(c.Address, 2, InStr(2, c.Address, "$") - 2) & c.Row & ":{" _
                            & c.Comment.Text & "}"
                    Next
                End If
            Next
        End If
    Next wksht
    Close #1
Exit Sub
err2_Save_AllSheetCommentsAsTextFile:
    bNoComments = True
    Resume Next
End Sub

[Jan Karel Pieterse]

Bad news. Not every element from
Selection.SpecialCells(xlCellTypeComments).Select
is in an area. Consequently the walk through the areas did not return everything in the selection

That is impossible. A range selection ALWAYS is part of an area. No exceptions.

[Paul_Hossler]

What kind of sort are you looking for? I'm confused. Are you trying to re-arrange Comments or cells containing Comments?

And out of curiosity, what are you wanting to achieve?

can I in somehow selection.sort this, ideally by cell addresses?

If there are comments in

A1
Z1
D1234
XFD1
XFD12345

A regular sort returns

A1
D1234
XFD1
XFD12345
Z1

So do you want

A1's comment into A1
Z1's comment into D1234
D1234's comment into XFD1
XFD1's comment into XFD12345
XFD12345's comment into Z1

[TheAntiGates]

Jan, thank you for following up. Following your code suggestion I used the following and it only got, for example, only 5 out of 20 comments on some sheets. I'll try to state more specifically what was omitted tomorrow. Perhaps single cells (with no adjacent ones) escaped recognition as areas below. This replaces the for/next loop in my code above.

Dim lct As Long
Dim oArea As Range
...
For Each oArea In Selection.Areas
   For lct = 1 To oArea.Cells.Count
      With oArea.Cells(lct)
       MsgBox "row:" & .Row & "; col=" & .Column
       Print #1, wksht.Name & " cell " & Mid(.Address, 2, InStr(2, .Address, "$") - 2) & .Row & ":{" _
      & .Comment.Text & "}" & IIf(Not .Comment.Visible, "(invisible)", "")
      End With
   Next
Next

Paul I marked it solved since the code in the most recent prior post above works. I understand how you interpreted the subject line, but actually the objective was just to sort the output, you know the #1 file as the code shows. Actually I decided not to "selection.sort" after all - sorry for the change-up.

[Jan Karel Pieterse]

Can you post the *complete* code which uses areas that did not work?

[TheAntiGates]

I'm forced to conclude that I have an "Error 1004: Idiotic User" because I cannot now reproduce the condition of truncated results. I must have been using a truncated file (less cells, less comments) to get less results. Nonetheless I will post the code, in shame and humility, since you asked. I humbly apologize for what now appears to be a false trail which was inexcusably in my control.

Sub test_Save_AllSheetCommentsAsTextFile()
    'Save all worksheets' comment info in a text file
    'Active sheet and selection are both affected
    Dim wksht As Worksheet, c As Range
    Dim i As Integer, j As Integer
    Dim strFileName As String
    Dim bNoComments As Boolean
    Dim lct As Long
    Dim oArea As Range
    
    Close #1    'redundant safety close
    strFileName = "foo.bar"
    Open strFileName For Output As #1   'open the file for output
    Print #1, "Comments in workbook " & ActiveWorkbook.Name
    'The following logic gives an unpredictably/inconsistently sorted order of output; other ways are under testing
    'walk each sheet;
    'Selection.SpecialCells(xlCellTypeComments).Select on each;
    'for each c in selection
    'print #1 "cell " & this...
    'Mid(c.Address,2,InStr(2,c.Address,"$")-2)
    ' & c.row & ":{" & c.comment & c.Comment.text & "}" & iif(not c.Comment.Visible,"(invisible)","")
    
#Const USE_JKP = true
#If USE_JKP Then
            Print #1, "area"   'JKP "Area" approach follows
#Else
            Print #1, "intersect"
#End If
    
    For Each wksht In Worksheets
        wksht.Activate
        bNoComments = False
        On Error GoTo err2_Save_AllSheetCommentsAsTextFile
        Selection.SpecialCells(xlCellTypeComments).Select
        On Error GoTo 0
        If Not bNoComments Then
#If USE_JKP Then
            For Each oArea In Selection.Areas
                For lct = 1 To oArea.Cells.Count
                    With oArea.Cells(lct)
                        'MsgBox "row:" & .Row & "; col=" & .Column
                        Print #1, wksht.Name & " cell " & Mid(.Address, 2, InStr(2, .Address, "$") - 2) & .Row & ":{" _
                            & .Comment.Text & "}" ' & IIf(Not .Comment.Visible, "(invisible)", "")
                    End With
                Next
            Next
#Else
            For j = 1 To ActiveSheet.UsedRange.Rows.Count
                If Not Intersect(Selection, Rows(j)) Is Nothing Then
                    For Each c In Intersect(Selection, Rows(j))
                        Print #1, wksht.Name & " cell " & Mid(c.Address, 2, InStr(2, c.Address, "$") - 2) & c.Row & ":{" _
                            & c.Comment.Text & "}" ' & IIf(Not c.Comment.Visible, "(invisible)", "")
                    Next
                End If
            Next
#End If
        End If
    Next wksht
    Close #1
Exit Sub
err2_Save_AllSheetCommentsAsTextFile:
    bNoComments = True
    Resume Next
End Sub

[Jan Karel Pieterse]

:-) I'm just glad VBA works as expected