How to select a user defined range

[simonriat]

Hi all

Hope someone can help.

Been using excel for years but very very new to VBA. Ive managed to record macros, but thats about the extent of my knowledge.

Can I ask? Is there any code that will enable me to ask a user for a range of blank cells to merge. Here is what I have so far....

Dim lp As Integer
Dim loops As Integer

loops = InputBox("Enter Number of Loops")


Do
    ActiveCell.Offset(0, 0).range("A1:A12").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    ActiveCell.Offset(1, 0).range("A1").Select
    lp = lp + 1
Loop Until lp = loops

As you can see I can get it to loop a user defined number of times, but it would be nice to be able to change the number of cells being merged.

Any help would be much appreciated.

Thanks
Simon

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

I also suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)