VBA For Filtering Comments in a Column

[mayoung]

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]

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]

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]

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]

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]

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.

I will try it and let you know..

 

[Micron]

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]

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

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

Link To Work Book

 

[Micron]

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

 

[mayoung]

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]

Link To Work Book

Yes code not working..