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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,228
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,129,714
Messages
5,637,940
Members
416,993
Latest member
ant8989

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