Consulting

Results 1 to 20 of 20

Thread: Solved: Programmatically Erase Data in Merged Cells

  1. #1
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location

    Solved: Programmatically Erase Data in Merged Cells

    I keep getting errors "cannot change part of a merged cell"
    ~Anne Troy

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    Nope you can't do that. You could use a selection instead.
    [vba]
    cell.Select
    Selection.ClearContents[/vba]

    There's also other ways around it. Let me know if you need something else.

    Edit: Btw, if you need to, post your code or an example of it. Or explain what you're trying to do with merged cells. Also, this is a big selling point for me advocating to stay away from merged cells as much as possible.

  3. #3
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    when I used next line on the merged cell I retrieve the same error as you retrieve:
    [VBA]ActiveCell.ClearContents [/VBA]

    but when using the following line it goes well:
    [VBA]ActiveCell = "" [/VBA]

    perhaps this helps.
    Jeroen

  4. #4
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    [vba] ActiveSheet.Unprotect Password:=""
    Range("rng1").ClearContents
    ActiveSheet.Protect Password:=""
    [/vba]
    ~Anne Troy

  5. #5
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    When I do the = "", I get "does not support this property or method"
    ~Anne Troy

  6. #6
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    I can send the workbook to anyone who wants to help.
    ~Anne Troy

  7. #7
    VBAX Regular
    Joined
    May 2004
    Location
    Springfield, MO
    Posts
    39
    Dreamboat,
    Please send the workbook.
    Brad

  8. #8
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Sent!
    ~Anne Troy

  9. #9
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Brad: I said it was the gray cells. And it is, except for the one at top-right. Leave that one alone.
    ~Anne Troy

  10. #10
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Thanks, Brad!! It appears to work. I forwarded to the owner. She's gone for quite some time, but we'll probably find out tonight yet how she fares.

    CANNOT thank you enough!
    ~Anne Troy

  11. #11
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Oh...and for those listening in, here's Brad's code:

    [vba]Sub EraseData()
    Dim cel As Range
    Application.ScreenUpdating = False 'Stops screen flicker & code runs faster
    ActiveSheet.Unprotect Password:=""
    Range("rng1").ClearContents
    Application.EnableEvents = False 'Don't run Worksheet_Change sub while clearing rng2, rng3, rng4, rng5 & rng6
    Range("rng2").ClearContents
    For Each cel In Range("rng3") 'Contains merged cells, so do this to avoid error
    cel.MergeArea.ClearContents
    Next cel
    For Each cel In Range("rng4")
    cel.MergeArea.ClearContents
    Next cel
    For Each cel In Range("rng5")
    cel.MergeArea.ClearContents
    Next cel
    For Each cel In Range("rng6")
    cel.MergeArea.ClearContents
    Next cel

    Application.EnableEvents = True
    ActiveSheet.Protect Password:=""
    Application.ScreenUpdating = True
    End Sub[/vba]
    ~Anne Troy

  12. #12
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    Hi Anne, it wont matter in this case as you are simply clearing contents, but when you loop through a range it looks at every cell individually, even if the cell is merged. So if A1:A3 were merged and A4:A5 were merged then a loop through A1:A5 is looping through 5 cells not 2.

    Ideally you would check to see if the cell was merged, then loop through the merged area seprately. This is just FYI...

    [VBA]Sub Example()
    Dim Rng As Range, c As Range, cell As Range
    Set Rng = Range("A1:A6")
    For Each c In Rng
    'you should put an IsError check here if you are looking at cell values
    If c.MergeCells Then
    If c.Address = c.MergeArea(1).Address Then
    For Each cell In c.MergeArea
    'do what ever to merged cell
    Next cell
    End If
    Else
    'do what ever to non merged cell
    End If
    Next c
    End Sub [/VBA]

  13. #13
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hi, parry! Thanks! Then....how do I tell it all those named ranges are my range, instead of just A1:A6?
    ~Anne Troy

  14. #14
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    Hi Anne, change this line...
    [VBA]Set Rng = Range("A1:A6") [/VBA]
    to this to join all the cells into one big range...
    [VBA]Set Rng = Union(Range("Rng3"),Range("Rng4"),Range("Rng5"),Range("Rng6")) [/VBA]
    The code you posted will work, but it just means its clearing the merged cells several times (once for each cell in the merged area). Not a biggie on a small range but may be slightly slower for a larger range.

  15. #15
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    Because you are just clearing contents you dont need to loop through each cell within the Merged area so heres a better example. You may still want to include the other actions such as screenupdating, turning off events etc.

    [VBA]Sub BetterExample()
    Dim Rng As Range, c As Range
    Set Rng = Union(Range("Rng3"), Range("Rng4"), Range("Rng5"), Range("Rng6"))
    For Each c In Rng
    If c.MergeCells Then
    If c.Address = c.MergeArea(1).Address Then c.MergeArea.ClearContents
    Else
    'do what ever to non merged cell
    End If
    Next c
    End Sub [/VBA]

  16. #16
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Well, Parry, to be honest, I always have TWO goals in a case like this.
    One--the best way.
    Two--the best way for this job. Since this job is just these ranges...I'll let it go, since I already sent her the file.

    But it's good ('specially for me who's learning) to get the *best* way.

    When this workbook is all done, we have her permission to "genericize it" and load it up as a sample...
    ~Anne Troy

  17. #17
    VBAX Regular
    Joined
    May 2004
    Location
    Springfield, MO
    Posts
    39
    Anne,
    I checked each of those named ranges and saw that they didn't contain very many cells. I recognized that I was repeating the effort for each cell in the merged range, but felt that it would be plenty fast enough for the purpose at hand. And certainly easier to maintain than creating separate ranges for each of the groups of merged cells.

    I agree that Parry's use of Union is a good simplification.
    Brad

  18. #18
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Cool.

    So both goals accomplished (so far).

    I'll let y'all know what she says.

    By the way, she made a BIG contribution to the site--enough to pay the past month of advertising the VBA Training.
    ~Anne Troy

  19. #19
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location

    Talking Coping With Blank Cells

    Quote Originally Posted by byundt
    I checked each of those named ranges and saw that they didn't contain very many cells.
    Brad
    Hi Brad.

    Anne, if there are cells with no data, then after the union do a intersect with used range, assuming the ranges are on the same sheet...

    [VBA]Set Rng = Union(Range("Rng3"), Range("Rng4"), Range("Rng5"), Range("Rng6"))
    Set Rng = Intersect(Rng, Rng.Parent.UsedRange)[/VBA]

  20. #20
    VBAX Regular
    Joined
    May 2004
    Location
    Springfield, MO
    Posts
    39
    Parry,
    Use of Intersection is good advice, but not relevant in this situation. Anne is working on a spreadsheet form, and the used range includes cells containing label information above, below, right and left of the merged cells under discussion.
    Brad

Posting Permissions

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