Home

Merge cells

%3CLINGO-SUB%20id%3D%22lingo-sub-893651%22%20slang%3D%22en-US%22%3EMerge%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893651%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20creating%20a%20data%20entry%20excel%20workbook.%20The%20work%20book%20consists%20of%202%20sheets.%20In%20%22sheet%201%22%20I%20want%20to%20write%20the%20number%20of%20cells%20to%20be%20merged%20in%20%22sheet%202%22%20after%20clicking%20a%20button%20in%20%22sheet%201%22.%20For%20example%2C%20if%20I%20wrote%20the%20number%205%20in%20%22sheet%201%22%20and%20I%20clicked%20the%20button%20then%205%20cells%20should%20be%20merged%20in%20%22sheet%202%22%20starting%20form%20cell%20%22C5%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20this%20could%20be%20wrote%20in%20VBA%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-893651%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893850%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893850%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420144%22%20target%3D%22_blank%22%3E%40abanoubemad%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20do%20this%20using%20the%20below%20code%20after%20assigning%20it%20to%20a%20button%20in%20the%20Sheet1%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-css%22%3E%3CCODE%3ESub%20MergeCells()%0A'%20Keyboard%20Shortcut%3A%20Ctrl%2BShift%2BM%0A%0ASheets(%22Sheet2%22).Range(%225%3A5%22).UnMerge%0A%0A%20%20%20%20Dim%20i%20As%20Integer%0A%20%20%20%20i%20%3D%20Sheets(%22Sheet1%22).Range(%22A2%22).Value%0A%20%20%20%20%0A%20%20%20%20Sheets(%22Sheet2%22).Activate%0A%20%20%20%20Range(%22C5%22).Resize(1%2C%20i).Select%0A%20%20%20%20Selection.Merge%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135454iB54BC5975A24D6B2%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22MergeCells.png%22%20title%3D%22MergeCells.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893881%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893881%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420144%22%20target%3D%22_blank%22%3E%40abanoubemad%3C%2FA%3E%26nbsp%3BMerging%20cells%20is%20generally%20a%20bad%20idea%2C%20but%20here%20you%20go%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20MergeNCells()%0ADim%20x%20As%20Long%0A%0Ax%20%3D%20Application.InputBox(%22How%20many%20cells%20do%20you%20want%20to%20merge%3F%22%2C%20%22Merge%20Cells%22%2C%201%2C%20Type%3A%3D1)%0A%0ASheets(%22Sheet2%22).Range(%22C5%3AC%22%20%26amp%3B%20x%20%2B%204).MergeCells%20%3D%20True%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ENote%20that%20it%20doesn't%20check%20if%20any%20cells%20have%20been%20previously%20merged.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
abanoubemad
New 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

Highlighted

@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.

Related Conversations
Linking Adjacent Rows
ashwinash112 in Excel on
1 Replies