checking any cell has comment in the whole column

osborne

New Member
Joined
Jan 18, 2021
Messages
2
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hi all. Need help on the following. I would like to write a code that if any cell in column AP has comment, it will prompt a msg box saying has comment. If no comment in any cell in column AP, it will prompt a msgbox saying no comment. As long as any cell in the column AP has comment, it will stop the loop. Somehow i feel it cannot use range. Pls help all the vba expert. Thank u in advance!

lastrow = Range("AP" & Rows.Count).End(xlUp).Row
If Range("AS4:AS" & lastrow).Comment Then
MsgBox "Has comment"
Else
MsgBox "no comment."
End If
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub v()
Dim rng As Range
On Error Resume Next
Set rng = Range("AS4:AS" & Cells(Rows.Count, "AP").End(xlUp).Row).SpecialCells(xlCellTypeComments)
If rng Is Nothing Then: MsgBox "No comment": Else: MsgBox "Has comment"
End Sub
 
Last edited:
Solution

osborne

New Member
Joined
Jan 18, 2021
Messages
2
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
VBA Code:
Sub v()
Dim rng As Range
On Error Resume Next
Set rng = Range("AS4:AS" & Cells(Rows.Count, "AP").End(xlUp).Row).SpecialCells(xlCellTypeComments)
If Not rng Is Nothing Then: MsgBox "Has comment": Else: MsgBox "No comment"
End Sub
thank u sir!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,307
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top