Range problem

[Valeria]

Dear experts,
I have a problem with the "range" Object.
I would like to find out the extent of merged cells with the following:
Do
a = a + 1
Loop Until Range(Worksheets("ASN").Cells(k + a, 2)).MergeCells = False

But I get an error: "Method "Range" of object "_Global" failed"

What does this mean? Where is the problem?
Many thanks in advance for your help.
Kind regards,
Valeria

 

[Rick Rothstein \(MVP - VB\)]

Are you looking to find out how many rows and columns there are in your
merged area or, perhaps, what range of cells it is composed of? If so, you
don't need a loop to do that. Consider this (where the Range points to any
cell within the merged area (I used k and 2 because your example seems to
indicate that cell is in your merged area)...

With Range(Worksheets("ASN").Cells(k, 2)).
NumOfRows = .MergeArea.Rows.Count
NumOfColumns = .MergeArea.Columns.Count
AddressRange = .MergeArea.Address
End With

Rick

 

[Valeria]

Hi Rick,
I have tried but it gives me an error - "syntax error"
I am using Excel 2003
Thanks
Kins regards

 

[Rick Rothstein \(MVP - VB\)]

Actually, I didn't test what I posted; I just used your structure. As it
turns out, the syntax for Range in the With statement seems to be wrong. Try
using this instead...

With Worksheets("ASN").Cells(k, 2)
Debug.Print .MergeArea.Rows.Count
Debug.Print .MergeArea.Columns.Count
Debug.Print .MergeArea.Address
End With

Rick

 

[Valeria]

It works!!!
Thanks a lot.
Kind regards