Consulting

Results 1 to 9 of 9

Thread: Solved: Clearing Contents of Merged Cells

  1. #1

    Solved: Clearing Contents of Merged Cells

    Hi all,

    When I try to execute the following line of code...

    [VBA]Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38").ClearContents[/VBA]

    I get an runtime error message "Cannot change part of a merged cell"

    Anyone know how to get round this??

    Thanks

  2. #2
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hi BexleyManor,

    One (or more) of the cells must be part of a merged cell. If you don't need it merged you could use code such as

    [vba]Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38").MergeCells = False
    Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38").ClearContents[/vba]

    However if you want it to remain merged after clearing it, you could use

    [vba]Dim cLL As Range, mergeAddress As String
    For Each cLL In Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38").Cells
    If cLL.MergeArea.Address <> cLL.Address Then
    mergeAddress = cLL.MergeArea.Address(0, 0)
    cLL.MergeCells = False
    cLL.ClearContents
    Range(mergeAddress).MergeCells = True
    Else
    cLL.ClearContents
    End If
    Next[/vba]

    Matt

  3. #3
    Hi Matt,

    Many thanks for the prompt input, much appreciated. I tried your second suggestion which worked. However, I notice it takes a while for the code to run which my users would immediately panic and set to their machine with a hammer, I kid you not!!

    Is there a quicker way of performing this action or will I need to confiscate all hammers on site??!!

    Many Thanks

  4. #4
    Actually, scrap that last comment. (Unless somebody comes up with something else!!)

    I used the application.screenupdating and that should run it smooth enough for even my most lairy of users!!!

    Thanks once again Matt, you the man!

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    If you wanted to get really fancy about it, you could make a userform that says something like 'Processing...' and display it before everything starts, then take it down when everythings done.

  6. #6
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Thinking a little more about it, you could always just do

    [VBA]Sheets("Jnl").Range("B3,I3,C41,H41,C7:H38") = ""[/VBA]

    Should accomplish what you need
    Matt

  7. #7
    Thanks for the further input Matt. Being a 'quick fix' merchant, your last suggestion suits me down to the ground!!

    Zack, thanks for the suggestion mate but If my users saw anything 'pop up' on their screen they'd probably level the office and run for the hills because the 'magic box had spoken tounges to them', I kid you not!!!

    Thanks fellas for the valued input.

  8. #8
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Quote Originally Posted by BexleyManor
    {Snip} If my users saw anything 'pop up' on their screen they'd probably level the office and run for the hills because the 'magic box had spoken tounges to them', I kid you not!!!{Snip}
    Your users must have trained mine. Believe it or not, I spent 10 minutes on the phone with one employee trying to get her to press the escape key. You would think that saying "press the key labeled Esc on the top-left of the keyboard would have worked".

  9. #9
    No Jacob, not trained, more likely they all escaped from the same mental institution, and in my case, they became accountants sent on a mission to make me bald and give me a nervous twitch!!

Posting Permissions

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