Merged Cells and using offset

  • So, I have tried to use the center across section and it will not work for the application and I have no choice but to use merged cells. center across section wont work because I want A4 B4 as one heading and c4 d4 as another and so on. When I do A4B4 as "center across section" and then c4d4 as another "center across section" it makes text in A4 center in between A4 and D4 not A4 B4.


    That being said. I am trying to have a cell checked for empty,
    if its empty, I want it to delete a picture, text, and pictures/text in comments.
    It works for the most part.
    but it stalls and doesnt complete the operation and not sure what i am missing.



    This is what I have. It works until the comment 'doesnt work past here.


    For reference, the A4 is where I want it to check for text. A4 is a merge of A4 and B4.


    If there is text I dont want it to do anything. If A4 is blank. I want it to delete the comment and pic from A5 and any text in B5 and any comment on B5.
    The offset command works for the contents of A5 but nothing works in relationship to B5.


    Ive messed with various variations using mergearea and also with out that function. cant seem to get it to step into B5.


    Thanks for any help

  • Would any of these be of any help? (Change references as required of course.)

    Code
    Range("A4").MergeArea.Cells(1,1).Value


    Code
    Range("A4:B4").Item(1).Value


    Code
    Range("A4:A11")(1).Value


    Do I read it right?
    A4:B4 is a merged range.
    but
    Rng.Offset(1, 1) (= B5), is not a merged range (as far as we know)
    However, in the code you make it as if it is

    Code
    Rng.Offset(1, 1).MergeArea(1, 1).Comment.Delete


    If you want to refer to B5, should you not use

    Code
    Rng.Offset(1, 1).Comment.Delete


    Try this maybe (With Comment in A5)

    Code
    If Not (Rng.Offset(1).Comment Is Nothing) Then Rng.Offset(1).Comment.Delete
  • Thanks for the reply. I have tried some variations of what you suggested with no luck.


    To clear up some of your questions.
    A4:B4 is merged together.
    A5 and B5 are both non merged cells. I am trying to have A5 will have a picture with a comment and B5 will have text with a comment. I am trying to have A5 and B5 cleared out (comments, text and picture all deleted) if cell A4:B4 is empty. The original code I posted works to clear out A5 with no issues. However the second part of the code does not do any delete of content in B5. I am pretty sure this has to do with the merged cells. But as I stated in my first post, I have no choice based on the design and issues with center over cells.


    Here is some cleaned up code to show what im trying to do. This code does not work to do anything in B5.


    As you can see, I have tried using "Rng.Offset(1,1).Coment.Delete" and "ClearContents" in order to delete both the contents and the comments in B5. But it is not doing anything. It doesnt give any errors either.

  • I may have found a work around.



    By resetting rng to "rng.offset" it then works. Since I know that the first part of the code worked to select A5, I just reset the new rng to A5 then offset 0,1 to select B5.


    If you have any cleaner ideas please let me know, but for now this appears to solve the issue.

  • I know, I tried to use the "Center across selection" to make this easier. But the problem is there is an issues with Center Across Selection formatting.
    For this particular project, A4 and B4 I need as one cell, then C4 and D4 as another cell. I selected A4 and B4 and used the Center Across Selection formatting. Then did the same for C4 and D4. When I entered text into A4, it centered across all 4 instead of just A4 and B4. Since there will be times that A4 will have text but not C4 and vice versa, this wont work for my needs and the merged cell is the only way to maintain the formatting I need.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!