Excel range setting fixed height changing location

Question

I am attempting to select a set number of rows in a changing location to merge. I cannot seem to figure it out. I have searched google for a half hour trying different ways of doing it. This is so I can merge and format the new combined 9 cells. It is under a button already and is going to be pulling information from a user form. What I am looking for is how to set the range. I am open to all suggestions thanks in advance.

Worksheets("CompList").Cells(Rows.Count, 1).End(xlUp).Select    'selects last row of information in row "A"
Cells(Selection.Row + BusHeight, Selection.Column).Select       'adds number of lines for business to add to get next empty slot

Selection.Value = 1 'listdivision.Value
    Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row + 1, Selection.Column), Cells(Selection.Row + 1, Selection.Column), Cells(Selection.Row + 1, Selection.Column), Cells(Selection.Row + 1, Selection.Column), Cells(Selection.Row + 1, Selection.Column), Cells(Selection.Row + 1, Selection.Column), Cells(Selection.Row + 1, Selection.Column)).Select

With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlTop
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
End With

I am lost with all those selects/selection. [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) Can you give me a simple example of what exactly are you trying to do? — Siddharth Rout, Nov 09 '13 at 06:19

score 0 · Answer 1 · answered Nov 09 '13 at 06:28

Is this what you are trying?

Sub Sample()
    Dim ws As Worksheet
    Dim LRow As Long, BusHeight As Long
    Dim Rng As Range

    Set ws = ThisWorkbook.Worksheets("CompList")

    BusHeight = 9

    With ws
        LRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

        Set Rng = .Range(.Cells(LRow, 1), .Cells(LRow - 1 + BusHeight, 1))

        With Rng
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlTop
            .ReadingOrder = xlContext
            .MergeCells = True
            .Value = 1
        End With
    End With
End Sub

Excel range setting fixed height changing location

1 Answers1