+ Reply to Thread
Results 1 to 10 of 10

Clear merged cell contents in comand button(clear all)

  1. #1
    Registered User
    Join Date
    08-23-2015
    Location
    BANGALORE
    MS-Off Ver
    2013
    Posts
    1

    Clear merged cell contents in comand button(clear all)

    Hiii FRIENDS

    REG: CLEAR MERGED CELL CONTENTS IN COMAND BUTTON(CLEAR ALL)
    I WANT TO CLEAR CELLS THROUGH COMMAND BUTTON(CLEAR ALL,WHICH ARE IN YELLOW COLOUR)
    PLS HELP TO CREATE A VBA CODE/MACRO.TXT
    * ALL CELLS SHOULD BE IN A SAME FORMAT,SO THAT I CAN EDIT FOR ANOTHER PERSON
    THAN CLEAR ALL....
    * I'M USING MS EXCEL 2013
    PLS HELP ME IN THIS REGARD
    THANK YOU
    Attached Files Attached Files

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Clear merged cell contents in comand button(clear all)

    Hi mohan_984

    Welcome to the Forum!!!

    There's probably a better way but I've not found it. Try this...
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Clear merged cell contents in comand button(clear all)

    This seemed to work well when I tested it.
    Maybe jaslake will let us know of any issues that might arise with this technique.

    Good Luck.

    Please Login or Register  to view this content.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Clear merged cell contents in comand button(clear all)

    Hi all,

    When it comes to VBA Code, merged cells are definitely more trouble than they're worth!

    Here's a slight modification of mohan's routine which avoids the need to select cells:

    Please Login or Register  to view this content.
    Hope this might be interesting / useful.

    Regards,

    Greg M

  5. #5
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Clear merged cell contents in comand button(clear all)

    Still looking for a downside with post #3.
    No looping.
    I did some testing on 600,000 cells of data and it was almost instantaneous, maybe 1 second.

  6. #6
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Clear merged cell contents in comand button(clear all)

    @jaslake - nice job. Excellent touch with the 'Select', but that seems to slow things down. In addition the clear is only required once for the merged area, not for each cell in the merged area.

    I tried two similar techniques, that only cleared the merged area, when the address of the cell was the address of the top left cell in the merged area. When benchmarking the results I removed the 'ScreenUpdating' code from the Sub, and put it in the test routine, since I was interested in the speed of the for loop.

    Benchmark results for 1000 iterations on my computer (Vista 32 bit, running Excel 2003):
    a. Original Technique - 6.39 seconds
    b. Technique 2 - 4.83 seconds - r.Value = ""
    c. Technique 3 - 3.88 seconds - c.MergeArea.ClearContents

    Complete code follows:
    Please Login or Register  to view this content.
    Lewis

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Clear merged cell contents in comand button(clear all)

    @Lewis

    Reason I said this is because I didn't like the Select...
    There's probably a better way but I've not found it
    I'll put these alternatives in my Tool Box.
    Last edited by jaslake; 08-23-2015 at 04:48 PM.

  8. #8
    Registered User
    Join Date
    08-23-2015
    Location
    Spain
    MS-Off Ver
    2007
    Posts
    32

    Re: Clear merged cell contents in comand button(clear all)

    My fastest shot! :P

    Include all merged cells in a defined sheet named range, only to do one first time (or after adding/deleting merged cells):
    Please Login or Register  to view this content.
    After that, you can clear the merged cells with just a single line:
    Please Login or Register  to view this content.
    Try to benchmark this ! xD
    Last edited by FerJo; 08-25-2015 at 07:22 AM. Reason: Sub NAME_MERGED updated
    May the REPUTATION be with me

  9. #9
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Clear merged cell contents in comand button(clear all)

    ferjo,

    Did you try it on the sheet provided by the OP?

    I think you have too many characters in your string (defRNG).

  10. #10
    Registered User
    Join Date
    08-23-2015
    Location
    Spain
    MS-Off Ver
    2007
    Posts
    32

    Re: Clear merged cell contents in comand button(clear all)

    Yes, you are right... too many merged cells = defined name string too long. Besides, I was adding the whole address of the merged area, not only the first cell (as Excel does).

    After checking the book (my mistake), I didn't read the complete description of the problem... I edited the Sub NAME_MERGED.

    Still, it won't be usefull in this case, since there is a yellow cell (G4) not merged... You can always create the named range manually instead! :P

    By the way, if just wanted to clear the yellow cells, wether they are merged or not, and avoiding cells with formulas, you can do it without a named range this way:
    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. [SOLVED] Using command button to copy cell contents and paste to different sheet, then clear cells
    By Ethanrholt in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-24-2015, 10:20 AM
  2. [SOLVED] Clear button, add warning dialogue box that asks: Are you sure you want to clear?
    By nenadmail in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-21-2012, 02:41 PM
  3. Button to clear sheet and automatic clear rows
    By sandbach in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-29-2010, 02:06 PM
  4. Clear Contents Macro-a Command Button
    By Andy Small in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2009, 05:29 AM
  5. creating clear contents button
    By ttik in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2008, 08:57 PM
  6. Clear Contents won't work on merged cells
    By ArthurJ in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2006, 01:25 PM
  7. [SOLVED] Clear cell contents with a button
    By KFC / A&W Boy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-07-2005, 06:06 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