Unable to merge cells

[briannnnleong]

I am intending to merge every cells between the two objects "FirstCell" and "SecondCell". Still a relatively new user to vba, hence not really sure what went wrong. I have also declared "EmptyRow" in a previous function.

Thank you!!!

 

[Zot]

What you were trying to align here? The syntax is not correct. Should be something like

Sub MergeCells()

Dim FirstCell As Range
Dim SecondCell As Range

Set FirstCell = Cells(EmptyRow, 2)
Set SecondCell = Cells(EmptyRow + 8, 2)

With Range(FirstCell, SecondCell)
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With

End Sub

You have
xlLeft
xlRight
xlJustify
xlCenterAcrossSelection

 

[briannnnleong]

What you were trying to align here? The syntax is not correct. Should be something like

Sub MergeCells()

Dim FirstCell As Range
Dim SecondCell As Range

Set FirstCell = Cells(EmptyRow, 2)
Set SecondCell = Cells(EmptyRow + 8, 2)

With Range(FirstCell, SecondCell)
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With

End Sub

You have
xlLeft
xlRight
xlJustify
xlCenterAcrossSelection

Thanks for your help but I'll have an error saying "Application-defined or object-defined error" on the statement of set firstcell = ...

 

[Michael M]

If you hover over "emptyrow" what does it show ?
Where is emptyrow declared ?
Is it a public declaration ?

 

[Zot]

In your sub MergeCells(), EmptyRow is not defined. Where is this EmptyRow defined? Unless you declare as Public variable, you need to pass this value into subroutine

 

[Zot]

This how you can pass the variable value into subroutine

Excel VBA Programming - Passing values to a Subroutine