Can I find an Excel merged cell size in VBScript?

15 pts.
Tags:
Microsoft Excel
VBScript
I have a set of Excel spreadsheets which all have similar properties and I'm trying to automate a set of processes on them. I'm a compelte VB beginner, but it's going ok - I've just hit a stumbling block with the internet isn't being very helpful with. Once I have found a certain cell, is it possible to determine how many columns it is merged across? Many thanks for any help!
ASKED: December 8, 2009  11:12 AM
UPDATED: August 23, 2013  3:34 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

This might help.

Sub Macro1()

‘ Count Merged Cells

x = 1
Mergedcell = ActiveCell.Address 'Remember where we are
ActiveCell.Offset(1, 0).Activate 'go down 1 row
Do
ActiveCell.Offset(0, 1).Activate 'go one cell to the right
nextcell = ActiveCell.Address 'We will come back here later
ActiveCell.Offset(-1, 0).Activate 'go up one row
If ActiveCell.Address = Mergedcell Then 'If we are back where we started than that was a merged cell
x = x + 1
Else
Exit Do 'If the address is different then this is the end
End If
ActiveSheet.Range(nextcell).Select 'go back to the cell below the merged cell
Loop
MsgBox "merged cell spans " & x & " Cells"

End Sub

Cheers : Kirederf

_______________________________________________________________
Hi Bengreen,

A simpler approach is:

<pre>
Range("A4").Select 'Assumes "A4" is the cell you want to check
MsgBox "Merged cell spans " & Selection.Rows.Count & " Rows " & _
"and " & Selection.Columns.Count & " Columns."</pre>

CHatmaker: Beyond Excel

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bengreen
    Hi guys, thanks for your help. It turns out that I'm even more of a n00b than I first thought. I didn't even know what language I was working in. Apparently, it was VBA, not VBScript And VBA, it seems, has this functionality built right in:
     Cells(RowCount, ColCount).MergeArea.Columns.Count
    and this
     Cells(RowCount, ColCount).MergeArea.Rows.Count
    which is nice ... Thanks for your help anyway!
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following