# VBA For Filtering Comments in a Column



## mayoung (Dec 15, 2022)

I am using Office 365 excel Is there away to filter Comments NOT Notes in a Column? Can be using a helper column if necessary.
I have the ability to filter Notes in a column but want to filter Comments. 
Any help would be appreciated.


----------



## Micron (Dec 15, 2022)

By filter do you mean show only rows where a particular comment exists in a cell? Or highlight the row? Or select the row? 
If it involves multiple non-contiguous rows being selected, that's not something I've done - yet.
If you want a formula, I can't help with that but I think something can be done via code. Just not sure what that something is at the moment.


----------



## JEC (Dec 15, 2022)

This macro extracts every comment in column A and writes them to column C


```
Sub jec()
 Dim it, x As Long
 With Range("A2", Range("A" & Rows.Count).End(xlUp))
    ReDim ar(.Rows.Count, 0)
    For Each it In .Cells
       If Not it.CommentThreaded Is Nothing Then
         ar(x, 0) = it.CommentThreaded.Text
         x = x + 1
       End If
    Next
    Range("C2").Resize(x) = ar
  End With
End Sub
```


----------



## mayoung (Dec 15, 2022)

Micron said:


> By filter do you mean show only rows where a particular comment exists in a cell? Or highlight the row? Or select the row?
> If it involves multiple non-contiguous rows being selected, that's not something I've done - yet.
> If you want a formula, I can't help with that but I think something can be done via code. Just not sure what that something is at the moment.


Correct I am wanting to filter to show only rows that contain Comments (Not Notes) using a Macro.


----------



## Micron (Dec 15, 2022)

Gotta run out. Will see where this went when I get back.


----------



## JEC (Dec 15, 2022)

For filtering as desired (column A in this example)


```
Sub jec()
 Dim it, sp
 Set sp = Cells(99999, 1)
 
 For Each it In Range("A2", Range("A" & Rows.Count).End(xlUp))
    If it.CommentThreaded Is Nothing Then Set sp = Union(sp, it)
 Next

 sp.EntireRow.Hidden = True
End Sub
```


----------



## mayoung (Dec 15, 2022)

In Excel you have the option to add a Comment or Note..What I am wanting to filter is the rows with Comments. 
Any suggestions?


----------



## JEC (Dec 15, 2022)

Did you even try the codes?
It is only possible with VBA


----------



## mayoung (Dec 15, 2022)

JEC said:


> Did you even try the codes?
> It is only possible with VBA


I did try the all code so far it copied over the Notes not Comments?


----------



## JEC (Dec 15, 2022)

But my codes both work for comments.
Do the cells have purple corners?


----------



## mayoung (Dec 15, 2022)

I am using Office 365 excel Is there away to filter Comments NOT Notes in a Column? Can be using a helper column if necessary.
I have the ability to filter Notes in a column but want to filter Comments. 
Any help would be appreciated.


----------



## Micron (Dec 15, 2022)

It might help to know where the comments can be. I tried that code and it hid every row from 2 to 35 yet there are only 3 rows with a comment in any column.


----------



## mayoung (Dec 15, 2022)

JEC said:


> But my codes both work for comments.
> Do the cells have purple corners?


Yes. Mine only pulled the Notes over when I tried it.


----------



## mayoung (Dec 15, 2022)

Micron said:


> It might help to know where the comments can be. I tried that code and it hid every row from 2 to 35 yet there are only 3 rows with a comment in any column.



My Comments are in Column-C starting in row 2.  I have data from Columns A through BA


----------



## Micron (Dec 15, 2022)

This should raise a message box for every row that contains a comment. If you can test it on a limited range of rows/columns first then it can be tweaked. Change your sheet name from sheet8 to whatever you need. If the msgbox works, I should be able to switch to hiding the rows, but the code might need to be "dumbed" down (because I was in the process before you answered) so it's not currently limited to any particular column when looking for comments

```
Sub TestComments()
Dim Lcol As Long, Lrow As Long, i As Long
Dim ws As Worksheet, rng As Range, rnge As Range

Set ws = Sheets("Sheet8")
With ws
     Lrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
     Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End With

Set rng = Range(Cells(1, 1), Cells(Lrow, Lcol))
For Each rnge In rng.Cells
     If Not rnge.Comment Is Nothing Then
          MsgBox rnge.Row
     End If
Next

End Sub
```
.

I also don't seem to be using "i" any more so there's some cleanup to do as well.


----------



## mayoung (Dec 15, 2022)

Micron said:


> This should raise a message box for every row that contains a comment. If you can test it on a limited range of rows/columns first then it can be tweaked. Change your sheet name from sheet8 to whatever you need. If the msgbox works, I should be able to switch to hiding the rows, but the code might need to be "dumbed" down (because I was in the process before you answered) so it's not currently limited to any particular column when looking for comments
> 
> ```
> Sub TestComments()
> ...


I will try it and let you know..


----------



## Micron (Dec 15, 2022)

Another option you can try, where C is the only column you're worried about looking for comments. Again, you will need to modify sheet name. If you want the code to work on any sheet, then it needs further mods and to be placed in a standard module.

```
Sub TestComments2()
Dim Lrow As Long, i As Long

Lrow = Sheets("Sheet8").Cells(Rows.count, "C").End(xlUp).Row
For i = 2 To Lrow
   If Not Range("C" & i).Comment Is Nothing Then
      Rows(i).EntireRow.Hidden = True
   End If
Next

End Sub
```


----------



## mayoung (Dec 15, 2022)

I have included a link to my workbook. Both of you codes are in the only module. Please see what you think?



Micron said:


> Another option you can try, where C is the only column you're worried about looking for comments. Again, you will need to modify sheet name. If you want the code to work on any sheet, then it needs further mods and to be placed in a standard module.
> 
> ```
> Sub TestComments2()
> ...


Link To Work Book


----------



## Micron (Dec 15, 2022)

Something wrong with your link, I think. Nothing happens.
Why do you want me to look at it anyway? Not working?


----------



## mayoung (Dec 15, 2022)

mayoung said:


> I have included a link to my workbook. Both of you codes are in the only module. Please see what you think?
> 
> 
> Link To Work Book


Link To Work Book


----------



## mayoung (Dec 15, 2022)

mayoung said:


> Link To Work Book


Yes code not working..


----------



## mayoung (Dec 15, 2022)

I am using Office 365 excel Is there away to filter Comments NOT Notes in a Column? Can be using a helper column if necessary.
I have the ability to filter Notes in a column but want to filter Comments. 
Any help would be appreciated.


----------



## Micron (Dec 15, 2022)

Maybe that's because there are no cells in column c that have comments? At least not that I can see in what you provided for download.
EDIT - more bizarre - there's no code that I can see in your workbook.
#2 ok, I see comments now, but no code. The borders make it very had to see the indicators.


----------



## Micron (Dec 15, 2022)

I've run out of editing time on the prior post.

So dumb me - for the last 20 years or so, I'd click alt+i+m and what popped up was a comment box. When I did that in my test sheet, the code worked on those comments. What I have in my sheet is now called "notes" as of version 365. Now "comments" are threads of some sort.
I will have to see what I can learn about that, but first I'll try my code, which for some reason, I don't see in either of your sheets. If I put "notes" in my sheet, then it makes no sense why my "comments" code worked on that.


----------



## mayoung (Dec 15, 2022)

Micron said:


> Maybe that's because there are no cells in column c that have comments? At least not that I can see in what you provided for download.





Micron said:


> I've run out of editing time on the prior post.
> 
> So dumb me - for the last 20 years or so, I'd click alt+i+m and what popped up was a comment box. When I did that in my test sheet, the code worked on those comments. What I have in my sheet is now called "notes" as of version 365. Now "comments" are threads of some sort.
> I will have to see what I can learn about that, but first I'll try my code, which for some reason, I don't see in either of your sheets.


Thank You for your help


----------



## mayoung (Dec 15, 2022)

Micron said:


> I've run out of editing time on the prior post.
> 
> So dumb me - for the last 20 years or so, I'd click alt+i+m and what popped up was a comment box. When I did that in my test sheet, the code worked on those comments. What I have in my sheet is now called "notes" as of version 365. Now "comments" are threads of some sort.
> I will have to see what I can learn about that, but first I'll try my code, which for some reason, I don't see in either of your sheets. If I put "notes" in my sheet, then it makes no sense why my "comments" code worked on that.


Your TestComments2 hides the rows with Notes.  How can this be modified to hide the Rows without Notes. For my future use?


----------



## Micron (Dec 15, 2022)

What you have are termed CommentsThreaded, so try

```
Sub TestComments()
Dim Lrow As Long, i As Long

Lrow = Sheets("WIP-SUMMARY").Cells(Rows.Count, "C").End(xlUp).Row
For i = 2 To Lrow
    If Not Range("C" & i).CommentThreaded Is Nothing Then
       ' Debug.Print Rows(i).Address
        Rows(i).EntireRow.Hidden = True
    End If
Next

End Sub
```



mayoung said:


> Your TestComments2 hides the rows with Notes. How can this be modified to hide the Rows without Notes. For my future use?


Then I guess remove Not from the test:
If Range("C" & i).Comment Is Nothing Then
Rows(i).EntireRow.Hidden = True

I tried the other codes you were given but those did strange things, like hide all the rows up to around 325 so not sure what's going on there. I should have looked into CommentThreaded way back then but I missed the clues.


----------



## mayoung (Dec 15, 2022)

JEC said:


> For filtering as desired (column A in this example)
> 
> 
> ```
> ...



This code works.


----------



## mayoung (Dec 15, 2022)

Micron said:


> What you have are termed CommentsThreaded, so try
> 
> ```
> Sub TestComments()
> ...



Your code worked I just had to modify and remove the Not

    If Range("C" & i).CommentThreaded Is Nothing Then
       ' Debug.Print Rows(i).Address
        Rows(i).EntireRow.Hidden = True

Thanks Again.


----------

