×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Merge every 9 Cells Loop

Merge every 9 Cells Loop

Merge every 9 Cells Loop

(OP)
How could I modify this code to make it merge every 9 cells in one column or two/three columns if I like:
Dim iRow As Integer, lRowStart As Long, iColStart As Integer
Dim iMrgRws As Integer, iMrgCls As Integer, iColCnt As Integer
Dim iRwsCnt As Long

lRowStart = 2 'start row
iColStart = 1 'start column
iMrgRws = 9 '# rows to merge
iMrgCls = 2 '# columns to merge
iColCnt = 1 'column count
iRwsCnt = 1 'row count

With ActiveSheet
For iRow = lRowStart To iRwsCnt * iMrgRws Step iMrgRws
.Range(.Cells(lRowStart, iRow), .Cells(lRowStart + (iMrgRws - 1), iRow + (iMrgRws - 1))).Merge
Next
End With
End Sub
Any suggestion would be helpful?

RE: Merge every 9 Cells Loop

Hi,

Welcome to Tek-Tips.

I'd recommend using a table of values rather than hard coding data in your procedure.

CODE

Sub Somename()
    Dim r As Long, c As Integer 'merge area index
    Dim lRowStart As Long, iColStart As Integer 'start first merge area
    Dim iMrgRws As Integer, iMrgCls As Integer 'merge area size
    Dim iMrgVCnt As Long, iMrgHCnt As Integer 'merge area counts
    Dim lRow1 As Long, iCol1 As Integer, lRow2 As Long, iCol2 As Integer 'from/to rows & cols

    lRowStart = 2   'start row
    iColStart = 1   'start column
    iMrgRws = 9     '# rows to merge
    iMrgCls = 2     '# columns to merge
    iMrgVCnt = 2    'merge vertical count
    iMrgHCnt = 3    'merge horizontal count
    
    With ActiveSheet
        For r = 0 To iMrgVCnt - 1                       'vertical merge counter loop
            lRow1 = lRowStart + r * iMrgRws             'merge from row
            lRow2 = lRowStart + (r + 1) * iMrgRws - 1   'merge to row
            
            For c = 0 To iMrgHCnt - 1                       'horizontal merge counter loop
                iCol1 = iColStart + c * iMrgCls             'merge from column
                iCol2 = iColStart + (c + 1) * iMrgCls - 1   'merge to column
                
                .Range(.Cells(lRow1, iCol1), .Cells(lRow2, iCol2)).Merge    'merge range
            Next
        Next
    End With
End Sub 


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Merge every 9 Cells Loop

Here's a workbook w/ VBA using Named Ranges for each required parameter.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Merge every 9 Cells Loop

(OP)
This is awesome! Thank you so much! Yo man this code is amazing! Exactly what I was looking for. I really hope I can get this good and be able to code this out myself someday.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close