INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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.

Jobs

Excel - Loop thru cells and Merge

Excel - Loop thru cells and Merge

(OP)
I have a report in Excel where I would like to Merge cells (that's what users want, so be it) So basically the outcome would be something like this (Merge 2 cells for 12 months in a Year)

    A    B    C    D    E    F    G     H
1      |    |    |    |    |    |    |     |
2  xxxxxxxx | yyyyyyy | zzzzzzz | wwwwwwww |
3      |    |    |    |    |    |    |     |
 
I know I can do:
Range("A2:B2").Merge
Range("C2:D2").Merge
Range("E2:F2").Merge
...

but that's very tedious. I would rather loop thru cells across and Merge them in the loop.

Any ideas of how to do it?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel - Loop thru cells and Merge

You can pass a string variable to Range().

CODE --> pseudo_language

dim rc as long
rc = GetLastWorksheetRow '<- you and find implementations of this on the web

dim theRange as string

dim theRow as long
for theRow = 2 to rc
theRange = "A" & cstr(theRow) & ":" & "B" & cstr(theRow)
range(theRange).Merge
next theRow

You can get more complicated/generic with a variable to loop through each pair of columns dynamically.

RE: Excel - Loop thru cells and Merge

(OP)
Thanks mintjulep,

I've used the link you provided and ended with the code like this:

CODE

'12 Months 2 Columns each
'plus 2 Columns for Total
intM = 7
For intC = 3 To 2 + (12 * 2)
    If intC Mod 2 <> 0 Then
        Cells(33, intC) = PvA_GetLettings(recL, intM) 
        Range(Cells(33, intC).Address, Cells(33, intC + 1).Address).Merge
        
        intM = intM + 1
        If intM > 12 Then intM = 1
    End If
Next intC 

So the actual code is more complicated that stated originally because I deal with Fiscal Year and the data starts not in the first column, but it works like a dream thumbsup2

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel - Loop thru cells and Merge

You could slightly simplify the code:

CODE -->

intM = 7
With ActiveSheet ' if applies to
    For intC = 3 To 2 + (12 * 2) Step 2
        .Cells(33, intC) = PvA_GetLettings(recL, intM) 
        Range(.Cells(33, intC)).Resize(1, 2).Merge
        
        intM = intM + 1
        If intM > 12 Then intM = 1
    Next intC
End With 

combo

RE: Excel - Loop thru cells and Merge

(OP)
combo,
I tried your line and got Run-time error '1004':
Method 'Range' of object '_Application' failed. sad

So for now I will stick with mintjulep's solution

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel - Loop thru cells and Merge

Sorry, should be without "Range":
.Cells(33, intC).Resize(1, 2).Merge

combo

RE: Excel - Loop thru cells and Merge

Here's a generalized solution

CODE

'
    Dim iCol As Integer, lRowStart As Long, iColStart As Integer
    Dim iMrgRws As Integer, iMrgCls As Integer, iColCnt As Integer
    
    lRowStart = 2   'start row
    iColStart = 1   'start column
    iMrgRws = 1     '# rows to merge
    iMrgCls = 2     '# columns to merge
    iColCnt = 12    'column count
    
    With ActiveSheet
        For iCol = iColStart To iColCnt * iMrgCls Step iMrgCls
            .Range(.Cells(lRowStart, iCol), .Cells(lRowStart + (iMrgRws - 1), iCol + (iMrgCls - 1))).Merge
        Next
    End With 

Skip,

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

RE: Excel - Loop thru cells and Merge

here's with a year/month date

CODE

'
    Dim iCol As Integer, lRowStart As Long, iColStart As Integer
    Dim iMrgRws As Integer, iMrgCls As Integer, iColCnt As Integer
    Dim dStart As Date, iMoCnt As Integer
    
    lRowStart = 2   'start row
    iColStart = 1   'start column
    iMrgRws = 1     '# rows to merge
    iMrgCls = 2     '# columns to merge
    iColCnt = 12    'column count
    dStart = #3/1/2018#
    
    With ActiveSheet
        For iCol = iColStart To iColCnt * iMrgCls Step iMrgCls
            With .Range(.Cells(lRowStart, iCol), .Cells(lRowStart + (iMrgRws - 1), iCol + (iMrgCls - 1)))
                .Merge
                .NumberFormat = "yyyy mmm"
                .Value = DateSerial(Year(dStart), Month(dStart) + iMoCnt, 1)
                iMoCnt = iMoCnt + 1
            End With
        Next
    End With 

Skip,

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

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!

Resources

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