[VBA] Merge Cells

ExcelNooberino

New Member
Joined
Jan 2, 2019
Messages
21
Hi there everyone!

I was wondering if it is possible to merge cells through VBA code, cuz I've been trying to pull it off and it seems I'm getting nowhere, I probably need some extra help :LOL: So the thing is, I add two new rows at the same time with some data by clicking this button but I need to merge some of the cells in that specific row. Something like this:

Public Sub SumREGR_MAT()

Application.ScreenUpdating = False


Dim y2 As Integer
Dim Row2 As Integer


y2 = MAT_02.Application.WorksheetFunction.Max(Range("B:B"))
Row2 = MAT_02.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row


MAT_02.Cells(Row2, 2).Value = y2 + 1
MAT_02.Cells(Row2 + 1, 2).Value = y2 + 1
MAT_02.Cells(Row2, 3).Value = Now
MAT_02.Cells(Row2 + 1, 3).Value = Now
MAT_02.Cells(Row2, 4).Value = "=SUM(MAT_02_EXP!K:K)"
MAT_02.Cells(Row2 + 1, 4).Value = "=SUM(MAT_02_EXP!K:K)"
MAT_02.Cells(Row1, 5).Value = "ME5009AAAA0"
MAT_02.Cells(Row1 + 1, 5).Value = "ME5008AAAA2"
MAT_02.Cells(Row2, 6).Value = 0.5
MAT_02.Cells(Row2 + 1, 6).Value = 0.5

Application.ScreenUpdating = True

End Sub
What I need is to merge MAT_02.Cells(Row2, 3) and MAT_02.Cells(Row2 + 1, 3) for example...
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,892
Office Version
2007
Platform
Windows
Try this

Code:
Public Sub SumREGR_MAT()


    Application.ScreenUpdating = False
    Dim y2 As Integer
    Dim Row2 As Integer
    
    y2 = MAT_02.Application.WorksheetFunction.Max(Range("B:B"))
    Row2 = MAT_02.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
    
    MAT_02.Cells(Row2, 2).Value = y2 + 1
    MAT_02.Cells(Row2 + 1, 2).Value = y2 + 1
    'MAT_02.Cells(Row2, 3).Value = Now
    'MAT_02.Cells(Row2 + 1, 3).Value = Now
    
[COLOR=#0000ff]    MAT_02.Cells(Row2, 3).Resize(2).Merge[/COLOR]
[COLOR=#0000ff]    MAT_02.Cells(Row2, 3).Value = Now[/COLOR]
    
    MAT_02.Cells(Row2, 4).Value = "=SUM(MAT_02_EXP!K:K)"
    MAT_02.Cells(Row2 + 1, 4).Value = "=SUM(MAT_02_EXP!K:K)"
    MAT_02.Cells(Row1, 5).Value = "ME5009AAAA0"
    MAT_02.Cells(Row1 + 1, 5).Value = "ME5008AAAA2"
    MAT_02.Cells(Row2, 6).Value = 0.5
    MAT_02.Cells(Row2 + 1, 6).Value = 0.5
    
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,893
Messages
5,471,340
Members
406,757
Latest member
dizzane

This Week's Hot Topics

Top