Range in Excel VBA

Question

I have written this code to merge a few lines in each column, from column C to AZ.

For some reason the range does not match the one I was expecting, in this case the code merges the cells C8:C10 then D8:D12, E8:E12, and so on. lines = 2 in this example.

I don't understand why aren't the ranges matching if lines value is not changing inside the for.

Thanks!

For columns = 0 To 49


    Range(Range("C8").Offset(0, columns), Range("C8").Offset((lines), columns)).Select
         With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
        End With

next comlumns

Answer 1

Columns is a reserved word. And you said that this code did run?

If I change that to a valid variable then the code runs. The problem is the way you are using Offset

?[C8].offset(2).address after the way you merge will give you $C$12

Also avoid the use of .Select INTERESTING READ And not to mention fully qualify your objects. For example your range and cell objects are not fully qualified and may give you error.

I think, this is what you are trying to achieve?

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long, rw As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    rw = 2

    With ws
        For i = 3 To 52
            Set rng = .Range(.Cells(8, i), .Cells(8 + rw, i))

             With rng
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 90
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = True
            End With
        Next i
    End With
End Sub