+ Reply to Thread
Results 1 to 7 of 7

Check if cell in range is empty, if it is delete comment else format comment to

  1. #1
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    198

    Question Check if cell in range is empty, if it is delete comment else format comment to

    I am having trouble getting this simple thing to work.

    What I need:

    A macro that checks cells B9:B350, if the cell is empty then delete comment to the right and if cell contains anything then format comment.

    This is what i got so far:

    Please Login or Register  to view this content.
    I get a object variable or with block variable not set error, what can I do?

  2. #2
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Check if cell in range is empty, if it is delete comment else format comment to

    On phone, can't check, but perhaps:

    Please Login or Register  to view this content.
    Please click the * below if this helps

  3. #3
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    198

    Re: Check if cell in range is empty, if it is delete comment else format comment to

    Same error happening

    asd1.JPG
    asd2.JPG

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Check if cell in range is empty, if it is delete comment else format comment to

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    198

    Re: Check if cell in range is empty, if it is delete comment else format comment to

    Quote Originally Posted by AB33 View Post
    Please Login or Register  to view this content.
    Oh This works great!

    One more question, Can we optimise it that instead of cheking all the cells at once it would only check the currenttly active cell and 1 above it?

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Check if cell in range is empty, if it is delete comment else format comment to

    I do not know which one is the active cell. This code checks cells in columns B starting from 9 down to the bottom.

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    198

    Re: Check if cell in range is empty, if it is delete comment else format comment to

    Thank you AB33, this was helpful.

    However this is still a workaround for me, is there any way to build it straight in my custom function? (This function acts like VLOOKUP but also retrieves the comment from found cell)
    It gets the comment text fine, but messes up the formatting

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Disable Delete Comment, Edit Comment, Show/Hide Comment
    By Shanthan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2013, 06:12 AM
  2. Check if cell contains comment
    By dave1983 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-25-2013, 08:53 AM
  3. open a comment to cell range upon true condition then removing comment on false condition
    By ferrum_equitis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-08-2012, 12:55 AM
  4. How can i Check The Cell Has Comment
    By karthisucc9 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-07-2012, 07:33 AM
  5. Check cell for comment
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-15-2006, 10:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1