Oct 04 2019 08:29 AM
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?
Oct 04 2019 09:46 AM
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
Regards
Oct 04 2019 09:53 AM
@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.