CODEWITHSUNDEEP
excelvba
AM_Hawk
AM_Hawk

Reputation: 681

Adding to a column letter excel

Hi there I would like to merge seven cells together all the way across row 1. Instead of clickng A1 pressing shift, then right arrow key six times I'm thinking I can achieve this via a macro. I have written the following but am unsure how to add seven to the letter reference, especially once I get into the last seven ending on AA1, then it starts AA2 and continues on. The following code achieves what I would like but how can I introduce variables and a loop to store the letters as int, add six, then cast back to string and continue to the next set? How would I write While (End of row has not been reached)

Range("A1:G1").Select
With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
End With

Range("H1:N1").Select
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
End With

Upvotes: 1

Views: 611

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Is this what you are trying?

'R1 is the row of the first cell
'C1 is the column of the first cell
'R2 is the row of the second cell
'C2 is the column of the second cell

Sub Sample()
    Dim Rng As Range
    Dim ws As Worksheet
    Dim R1 As Long, C1 As Long
    Dim R2 As Long, C2 As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    R1 = 1: C1 = 1
    R2 = 1: C2 = C1 + 6

    With ws
        Set Rng = .Range(.Cells(R1, C1), .Cells(R2, C2))
        Application.DisplayAlerts = False
        Rng.Merge
        Application.DisplayAlerts = True
    End With
End Sub

EDIT: You might also find THIS link interesting.

Upvotes: 1

Related Questions