How to Dim a variable as a Cell?

Question

On Excel, I'm trying to code a macro that involves selecting a range of cells, in which both the upper limit and the lower limit cells in the range are variables. How would I go about programming this?

More specifically:

I have a column A and a column B. I wish to select a range of cells from column A and merge them together. That range will be equal to the range of cells in column B that are NOT empty. So, for example:

If cells B1 to B15 aren't empty (and B16 is empty), then my macro will select the cells A1 to A15 (then merge them).

As an added bonus, telling me how to code this activity would be even better =D

General_Payne · Accepted Answer

I might be misinterpreting your question but I'd do it like this.

Insert a module be by pressing Alt+F11 to bring up VB and clicking insert>module.

Paste this in the module:

Sub Merge()
Dim i As Long
On Error Resume Next
i = Range("B:B").Cells.SpecialCells (xlCellTypeConstants).Count
Range("A1:A" & i).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
End Sub

Close VB and to run the macro press alt+F8 and run the macro called "Merge".

Hope this is what you were after.

Not the best way but it gets the results you're after (assuming I understand what you're after).

Cheers

garbo7441 · Answer

Are you selecting the range of cells with the mouse, or do you want to evaluate column B and merge column A for every group of cells in column B bordered by blank cells?

Trending News

How to Dim a variable as a Cell?

2 Answers