Finding address of merged cells 3

[sglab]

Hello everyone,

I have a question for you. Is there a way to determine if a given range contains merged cells and retrieve the address of upper-left most cell?

Thank you in advance.

 

[SkipVought]

Hi,

You can use the MergeArea Property -- check it out in HELP.

if Cells(r, c).MergeArea.Count > 1 Then
  'houston, we have a merge area
else
  'houston, go back to sleep -- no merge area
end if

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[sglab]

Skip,

Thanks for your reply. It'll help me a lot, but I'm also interested in getting the address of mergearea. And also, what if there are more than 1 such area? How do I do that?

Thanks.

 

[SkipVought]

Address is a property of a range.

What are you trying to accomplish?

("I'm counting grains of sand" is, for instance, NOT an answer to "what are you trying to accomplish?" I'm building a vollyball court and I need to determin how much sand I'll need" is.)

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[sglab]

Skip,

It's quite a big task, at least for me. So bear with me. I've come to the question about merged cells while writing an application that would format Excel files in a certain way before converting them to TIFF images. The problem, that I'm trying to solve here is that since we converting Excel files in batches and don't know in advance their design and how rows and columns get populated with data, we have to apply some generic formatting before we start batch processing. Because of variety of Excel files and common settings for all of them, some of final TIFF images could contain blank pages - only columns' headers and grid. We're talking 1000s of Excel files and 1000s of blank pages. The idea was to find these blank pages after formatting Excel file and exclude them from conversion process. Right now I'm using horizontal and vertical page breaks to single out ranges with no info, but I've come to conclusion that ranges, containing merged cells, have to be formatted differently: I need them unmerged, text-wrapped and have their rows auto-fit.

I hope it was not too confusing. By the way, if you have any previous experience with something like that, I'd really appreciate your input on that.

Thanks.

 

[SkipVought]

This sounds like the problem to the test instrument affecting the the test.

If you change the column and row characteristics to accomodate a merge area, then you affect the paging. Could be kinda tricky.

What is the problem that you are encountering with "converting" merged text to a TIFF image?

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[sglab]

1. Yes you right, and that's why I'm dealing with all these merged areas before applying formatting. The problem is, I was doing it by checking all the cells in active worksheet one-by-one, which is obviously very-very time consuming. That's why I asked about getting addresses of merged cells, not just count - even though it could save a great deal of time also - so that I could avoid cell-by-cell iteration.
2. Briefly - there could be a situation where page break splits merged text between 2 pages.

Skip, unfortunately I have to leave for the day now. I hope that you'd still reply, so I could read what you think tomorrow.

Thanks for your time and have a great day.

 

[SkipVought]

I've thought about this one and I can't come up with any clever way to readily identify all merge areas.

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[Rofeu]

Hi,

not quite sure how long this would take with your load, but it's a lot faster then going cell by cell:

 Sub CheckMergedCells()

Dim Cell As Object

For Each Cell In Application.ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks)
    With Cell
        If .MergeCells Then
            .UnMerge
            .WrapText = True
            Rows(.Row).EntireRow.AutoFit
        End If
    End With
Next

End Sub

I hope it does you some good,

Cheers,

Roel

 

[sglab]

Hi Roel,

Thanks for your input. Perhaps my explanations were a little confusing; I get confused myself sometimes when the problems start to pile up: fix one - break something else.
Actually, when looking for empty ranges I also use xlCellTypeBlanks and it works much faster , but it really doesn't matter whether these empty ranges have merged cells or not. I'm more inerested in ranges that do have information and merged cells with info at the same time. The problem for me is that I can't seem to find any way to avoid cell by cell iteration within current range in order to find merged cells and apply formatting that you correctly specified in your code: unmerge, wrap, autofit. Only I set column width also.

Any thoughts?

Thanks again.

 

[Rofeu]

Hi,

I'm not quite sure I understand what it is you want, but the code I gave does look at merged ranges with data. The principal is, that in such a range, the first cell actually contains all the the data and the rest of the merged range is blank. Therefore, you don't need to check all the cells, just the blank ones. (if there none then there are no merged ranges)

Cheers,

Roel

 

[sglab]

Roel,
Thanks for not giving up on me.
Let me put my problem this way, I want to be able, without cell by cell iteration, to determine if a given range has merged cells. If it does, I need to know their address or addresses if there more then one merged area. Is that possible to do that?

Thanks

 

[Rofeu]

How about something like this:

Sub CheckMergedCells()

Dim MergeCell1 As String
Dim MergeCell1Value As String

Dim Cell As Object
Dim MergeAddress() As String
Dim I As Integer
ReDim MergeAddress(I) As String

For Each Cell In Application.ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks)
    
    With Cell
        If .MergeCells Then
            
        MergeCell1 = _
            Left(.MergeArea.Address, InStr(1, .MergeArea.Address, ":") - 1)
            
        MergeCell1Value = _
            Range(MergeCell1).Value

            If InStr(1, Join(MergeAddress, ","), MergeCell1) = 0 _
                                            And MergeCell1Value <> "" Then

                MergeAddress(I) = MergeCell1
                I = I + 1
                ReDim Preserve MergeAddress(I) As String
            
            End If
        End If
    End With
Next

MsgBox Join(MergeAddress, ",")

End Sub

Cheers,

Roel

 

[sglab]

Hi Roel,

Thanks.

I need some time to figure that one out.
In the meantime, could you take a look at a statement below as to whether this is the right/efficient way to determine if given range is empty:

 if activesheet.Range(Cells(tRow, tCol), Cells(bRow, bCol)).SpecialCells(xlCellTypeBlanks).Count = activesheet.Range(Cells(tRow, tCol), Cells(bRow, bCol)).Cells.Count Then
 <do something>
End if

Well, I know that it does work, but only when there are empty cells in the range. If the range doesn't have blanks, left part of the condition of this statement doesn't return 0, but gives an error, saying that empty cells were not found. What do you think is the best approach?

Thanks,

Sergey.

 

[PHV]

Have a look at the CountA WorksheetFunction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sglab]

Thanks PH,


Will do.

 

[sglab]

Hi PH,

I tried to use WorkSheetFunction in my app. Not CountA, though, but CountBlank. I think it worked accurate. At least I checked return values a dozen of times during the execution.
Thanks a lot for the tip.

Sergey.