Select a range of cells with the cursor then have those cells merge

[Edward Sky]

I'm looking for a vba solution. The reason I need to merge cells is to give me a final picture of what an end product would look like without manually selecting a range, pressing merge and center and then selecting another range etc... Is this possible?

Thank you.

 

[ParamRay]

Hello, this would go in the code module for the sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Target.Merge
End Sub

 

[L. Howard]

Maybe this. Where you would select the cell with the "text" or what ever and run the snippet.

This does not MERGE. it Centers Across Selection, with the "selection" being 1 row by 6 columns.

There are good reasons to avoid "merged" cells.

Howard

Sub Center_Me()
   With ActiveCell.Resize(1, 6)
     .HorizontalAlignment = xlCenterAcrossSelection
     .VerticalAlignment = xlBottom
    End With
End Sub

 

[JoeMo]

Sub SelectAndMerge()
Dim R As Range
On Error Resume Next
Set R = Application.InputBox("Select a contiguous range of cells to merge", Type:=8)
If R Is Nothing Then Exit Sub
If R.Areas.Count > 1 Then
    MsgBox "Selection must be a single contiguous range - goodbye"
    Exit Sub
End If
With R
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
End Sub

 

[Edward Sky]

Hello, this would go in the code module for the sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Target.Merge
End Sub

This was exactly what I was looking for.

One last thing, Is there a way for this to be restricted to a specific range of cells say from J15:N20?
That would be the incredible.
Thank you!!!

 

[Edward Sky]

Thank you Howard =)

 

[Edward Sky]

That was a very interesting solution JoeMo. Thank you!

 

[MARK858]

Silly question but why do you need to merge the cells and are they on the same row?
Because if the solution by L Howard works for you then I would totally avoid merged cells.

 

[Edward Sky]

Silly question but why do you need to merge the cells and are they on the same row?
Because if the solution by L Howard works for you then I would totally avoid merged cells.

Oh! Well umm. My friend works with making windows for a living, and because customers want different patterns made up within their window space, it's necessary to replicate that pattern on the spreadsheet to work out both the size of the glass that needs to be cut, and the amount of framework needed for the job. I wish I could attach a picture but I'm relatively new to forums and don't know how.

 

[MARK858]

How do you get an accurate measurement using merged cells