Adding to a column letter excel

Question

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

score 1 · Accepted Answer · edited May 23 '17 at 12:10

1

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.

edited May 23 '17 at 12:10

Community

1
1

answered Oct 18 '13 at 13:56

Siddharth Rout

147,039
17
206
250

can you address the loop aspect of my question. – AM_Hawk Oct 18 '13 at 15:14
Sure :) get the last row in that column and use a `For Next` loop :) To get the last row, see this link http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba – Siddharth Rout Oct 18 '13 at 15:15

Adding to a column letter excel

1 Answers1