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

use `Option Explicit` first of all and you'll see a typo which should be a large portion of your problems. — enderland, Dec 19 '13 at 19:48

score 2 · Answer 1 · edited May 23 '17 at 12:03

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

I don't think the OP's code could have run with the For/Next loop broken (note misspelling on the `Next` part) — enderland, Dec 19 '13 at 19:55
@enderland; Yeah I already noted that when I was testing it. I didn't mention it as I finally changed the damn offending variable :p — Siddharth Rout, Dec 19 '13 at 19:56
I added the typo in the browser. I forgot to copy this line and manually mistyped it. Weirdly enough (I'm not very familiar with VBA) the word columns as a variable is working fine. I understand though the idea of making a cleanr code and will study Siddaharth solution to learn something. I made it work yesterday but I have much more stuff to add and a cleaner code will definitely help me! Thanks! — GAS, Dec 20 '13 at 19:34

Range in Excel VBA

1 Answers1