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.Addressgave this
$G$2
$C$37
$D$39

but
For j = 1 To Selection.Cells.Count
Debug.Print Selection.Cells(j).Addressgave
$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:( :banghead:

[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:( :banghead: 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