excel range

[arshesander]

                Range("A2:D2").Select
                With Selection
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlBottom
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = True
                End With
                Selection.Merge

'ive been using that code merge a cell. but my problem is the cell, is possible to use "1" instead of "A" in defining cell range ("A22")?

because sometimes, i don't know what exact row or column to be merge.. it will base on the For Next statement of the code.

[Siddharth Rout]

Yes it is possible. Two Ways

Way One: When you know the column and don't know the row.

Remember Range("A1") can also be written as Range("A" & i). Here is an example.

Sub MergeCells()
    i = 1
    j = 4
                
    With Sheets(1).Range("A" & i & ":" & "D" & j)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.Merge
End Sub

Way Two: When you don't know the column and don't know the row.

Sub MergeCells()
i = 1
k = 4

ThisWorkbook.Worksheets("sheet1").Range(Cells(i, i), Cells(k, k)).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

End Sub

Hope this helps...

Edit: This should be in the office Dev forum. Request a mod to move it there

[si_the_geek]

Thread moved to Office Development/VBA forum (note that the "VB Editor" in Office programs is actually VBA rather than VB, so the VB6 forum is not really apt)