Merge cells

Copper Contributor

I am creating a data entry excel workbook. The work book consists of 2 sheets. In "sheet 1" I want to write the number of cells to be merged in "sheet 2" after clicking a button in "sheet 1". For example, if I wrote the number 5 in "sheet 1" and I clicked the button then 5 cells should be merged in "sheet 2" starting form cell "C5"

 

How this could be wrote in VBA?

2 Replies

@abanoubemad

 

Hi,

 

You can do this using the below code after assigning it to a button in the Sheet1:

Sub MergeCells()
' Keyboard Shortcut: Ctrl+Shift+M

Sheets("Sheet2").Range("5:5").UnMerge

    Dim i As Integer
    i = Sheets("Sheet1").Range("A2").Value
    
    Sheets("Sheet2").Activate
    Range("C5").Resize(1, i).Select
    Selection.Merge
End Sub

 

MergeCells.png

 

Regards

@abanoubemad Merging cells is generally a bad idea, but here you go:

 

Sub MergeNCells()
Dim x As Long

x = Application.InputBox("How many cells do you want to merge?", "Merge Cells", 1, Type:=1)

Sheets("Sheet2").Range("C5:C" & x + 4).MergeCells = True

End Sub

Note that it doesn't check if any cells have been previously merged.