How to merge cells in excel using VBA?

[SilentRomance]

Hi guys,

I am using VBA code to merge a cells below the row that has data.

I can't use specific value of the RANGE() because the data will be increased or decreased anytime.

This the code i"m using but when I run the code the macro give me a warning message

Sub Cert()

Dim cer As Long


cer = Cells(Rows.Count, 1).End(xlUp).Row + 1

With Range("cer + 2" & 1 & ",cer + 2" & 6 & "")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

End Sub

Can you help me about this
I am new in VBA language so sorry for the **** logic.

And also that merged cells have a value like "This all the total of credit".

 

[Crystalyzer]

you don't need the quotes in your Range declaration and if you want to use numbers for rows and columns you need to use the Cells object. Below is your code cleaned up and the example worksheet I created. The gray highlighted cells are the ones that were merged.

Sub Cert()
Dim cer As Long

cer = Cells(Rows.Count, 1).End(xlUp).Row + 1

With Range(Cells(cer + 2, 1), Cells(cer + 2, 6))
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

End Sub

Book7
	A	B	C	D	E	F
1
2
3
4	Any Data
5
6
7
8
9
10
11
Sheet1

 

[aRandomHelper]

Hi, there are a few mistakes in the code.

First, 'cer' is based on column A, which might be empty in your sample data -- meaning this will always give you a "1", and you will always merge the wrong cells.

Next, the String version of cell addresses might be tough to understand in VBA syntax for beginners, using the Cells function to return using row/column numbers can be less confusing.

Sub Cert()

    Dim cer As Long

    cer = Cells(Rows.Count, 3).End(xlUp).Row + 1 'now the last row is based on column 3 (C)
    
    With Range(Cells(cer + 2, 3), Cells(cer + 2, 5)) 'get the range of columns 3:5 (C:E), row = cer + 2
        .Merge
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Value = "This all the total of credit" 'this is to add the value
    End With

End Sub

 

[Michael M]

Further to the remedial posts, I would suggest to @SilentRomance that you consider avoiding using Mergede Cells and VBA as they are prone to issues that may adversely affect the perfornace of the code and / or the worksheet itself
Merged cells can cause all sorts of issues especially when filtering, formulas, etc
Instead of MergedCells try highlighting the desired cells, then using >>Format cells>>Alignment>>Horizontal dropdownBox>>"Center across selection"