Find Cell Value, Count No. Merged Rows, Select Offset Range

[DanGK1]

I have spent hours trying but cannot get there.

I have a spreadsheet of data which is an export from an external program and the layout off this export has merged cells.

I have found the code to count merged cells and managed to add find to that

Range("A:A").Find(What:="Magento").MergeArea.Rows.Count

What I am trying to do next is select a range based on that merged row count value - offset 2 columns (which is to column C, I can do that), then select a range which is the offset column, the column next to it and the number of rows (which are not merged) based off the value of the Merge Area Row Count. I tried to go back to basics and print the value of the row count to a cell but I couldn't get that to work either.

I welcome suggestions.

Thanks

 

[StephenCrump]

Is this what you're after?

Dim r As Range

With Range("A:A").Find(What:="Magento")
    Set r = .Offset(, 2).Resize(.MergeArea.Rows.Count, 2)
End With

MsgBox r.Address

(Although my preference would be strip out all the merged cells before doing anything!)

 

[DanGK1]

thanks Stephen.

I replaced the message box with

Range(r.Address).Select

And got just what I wanted (and yes, perhaps I could unmerge first...)