CODEWITHSUNDEEP
vbaexcel
AIRShaheen
AIRShaheen

Reputation: 19

VBA to Merge Two Cells that will always be in cells 2 rows beneath the last row with data in it

I am relative new to VBA. I am writing a macro and need to merge two cells in columns C and D that will always appear in the second row beneath the last row with data in it. The script below is what I am trying to do, but continue to receive an error message. Any assistance or ideas is appreciated. 

ActiveSheet.Range("C:D" & Lastrow + 2).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge

Upvotes: 0

Views: 89

Answers (2)

GMalc
GMalc

Reputation: 2628

No disrespect to @BigBen, But your recorded code can be cleaned up even more, most of the code added is default code and can be removed. Also, your range can be cleaned up using Cells, Offset, and Resize.

With Cells(lRow, 3).Offset(2).Resize(, 2)
    .HorizontalAlignment = xlCenter
    .Merge
End With

Upvotes: 1

BigBen
BigBen

Reputation: 50008

"C:D" & LastRow + 2 is not a valid Range reference. And no need to Select.

With Range("C" & LastRow + 2 & ":D" & LastRow + 2)
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .Merge
End With

Upvotes: 4

Related Questions