-
Aug 19th, 2021, 04:51 PM
#1
Thread Starter
Junior Member
[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?
-
Aug 20th, 2021, 05:30 AM
#2
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
-
Aug 20th, 2021, 06:42 AM
#3
Thread Starter
Junior Member
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!
-
Aug 20th, 2021, 09:15 PM
#4
Thread Starter
Junior Member
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!
-
Aug 21st, 2021, 03:59 AM
#5
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
-
Aug 21st, 2021, 06:26 AM
#6
Thread Starter
Junior Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|