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
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
Highlighted

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies