CODEWITHSUNDEEP
vbaexcel
Ans
Ans

Reputation: 1234

Application defined or object-defined error mergedcells

I have the following code that either takes an ActiveCell and MsgBoxes its MergeArea borders (works fine), or takes a predetermined range and does the same (fails). When I try the latter I get application defined or object-defined error. 

Sub test2()
     Dim Titles As Range
     Set Titles = Range("E13:H13")

     Dim titlesMerge As Range
  '  Set titlesMerge = ActiveCell.MergeArea   'this works fine
     Set titlesMerge = Titles.MergeArea  'when use Range("E13:H13") it 
                              'fails

     MsgBox (titlesMerge.Row & " and " & titlesMerge.Rows.Count)
End Sub

Cells "E13:H13" are merged.

What is wrong here?

Upvotes: 1

Views: 395

Answers (1)

Romcel Geluz
Romcel Geluz

Reputation: 603

To my curiosity about Sir Siddharth Rout's comment, I experimented with this code.

Sub caller()
Dim rMergedCell As Range
' Let B3:C4 a merged cell
Set rMergedCell = Range("B3:C4")

  Dim rTitlesCell As Range
  Set rTitlesCell = rMergedCell.Cells(1, 1).MergeArea

  MsgBox rTitlesCell.Row & " and " & rTitlesCell.Rows.Count
' Returns 3 and 2
End Sub

He remarked that the MergeArea property only works on a single-cell reference. So why not get the most-upper-left cell of the Merged cell? Hope this helps the OP.

Upvotes: 1

Related Questions