Results 1 to 6 of 6

Thread: [RESOLVED] EXCEL2007 Win7 Merged Cells: Test 2 rows in vertically merged cell is inconsistent!

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2018
    Posts
    25

    Resolved [RESOLVED] EXCEL2007 Win7 Merged Cells: Test 2 rows in vertically merged cell is inconsistent!

    I'm trying to convert rows containing vertically merged cells to single, unmerged rows. For years my code worked but a few weeks ago it failed. I grab the top row value and next row value and compare to see if they are equal; my impression is they should be, and have been for many years. But, I guess an update a few weeks ago changed things. But! While I was testing by walking through the code I did see one pass where the value retrieved for the cell was the same for each row. The next pass ( and the rest of my tests! ) through the same code failed because they weren't the same - even though the "Formula Bar" reports the same value for each row:
    gobjEXCELWorksheet.Cells(plTemp1 , 3).Select
    pzTemp = gobjEXCELWorksheet.Cells(plTemp , 3).Value

    gobjEXCELWorksheet.Cells(plTemp1 + 1, 3).Select
    pzTemp1 = gobjEXCELWorksheet.Cells(plTemp + 1 , 3).Value

    When it doesn't work the pzTemp1 value = "" but the "Formula Bar" reports the value in the Merged Cell. I actually "reset" the Formula Bar between retrievals and it still consistently shows the same cell values for both rows while my retrieval code does not!

    Anybody have hep with either a better retrieval method or an alternate test for a Merged Cell?

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: EXCEL2007 Win7 Merged Cells: Test 2 rows in vertically merged cell is inconsiste

    you can test if a cell is merged like
    Code:
    if rng.MergeCells then
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Junior Member
    Join Date
    May 2018
    Posts
    25

    Re: EXCEL2007 Win7 Merged Cells: Test 2 rows in vertically merged cell is inconsiste

    Thank you. I looked for a "MergedCell" test, but I guess I'm not imaginative enough! I'll check it out. But the change in behavior of retrieving the value in a merged cell is annoying - even more so when it changed between stepthroughs being carried out in the same session! The value presented in the Formula Bar behaved as expected, so I was about to embark on a search to retrieve the display in the Formula Bar!

  4. #4

    Thread Starter
    Junior Member
    Join Date
    May 2018
    Posts
    25

    Re: [RESOLVED] EXCEL2007 Win7 Merged Cells: Test 2 rows in vertically merged cell is

    westcomm1: I tried it and it worked! Thanks muchly. maybe you could offer me another aid - when I first started using EXCEL I used a Property/Method that provided the the count of active rows on a sheet - without actually counting them. Then I didn't use EXCEL for a few years and I couldn't find that trick again!

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] EXCEL2007 Win7 Merged Cells: Test 2 rows in vertically merged cell is

    maybe
    Code:
    activesheet.usedrange.rows.count    ' or whatever sheet object
    mind sometimes it may lie, if rows have been used but the values cleared, or there are blank rows at the top of the worksheet, i did a heap of testing on this issue a short while ago, if you search in this forum, if there are blank rows at the top, you can add the count of rows to the first row like
    Code:
    with activesheet
        .usedrange.rows.count + .usedrange.row
    end with
    the other method to find the last row
    Code:
    lastrow = activesheet.cells(rows.count, 1).end(xlup).row
    you may need to change the column # from 1 if col A does not contain date to the length of some other rows

    the text property of the merged cells may still work as it did previously even if the value does not
    Code:
    pzTemp1 = gobjEXCELWorksheet.Cells(plTemp + 1 , 3).Text
    Last edited by westconn1; Aug 21st, 2021 at 04:03 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Junior Member
    Join Date
    May 2018
    Posts
    25

    Re: [RESOLVED] EXCEL2007 Win7 Merged Cells: Test 2 rows in vertically merged cell is

    westcomm1: Thanks more! I'll try these...

Posting Permissions

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



Click Here to Expand Forum to Full Width