VBA Merge Range cells in Excel explained with Examples

VBA Merge range or cells in Excel will merge multiple cells in a excel worksheet using merge method of range object and then creates a merged cell. ‘Range.Merge’ method will merge the multiple cells or range, and then after merging it will make it as one singular cell at upper left corner of the range .

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


VBA Merge Method Excel Range Object

VBA Merge Range – Syntax

Here is the syntax to merge range or multiple cells in a worksheet. You can merge the multiple cells including the formats using ‘Range.Merge’ method.

Range(“YourRange”).Merge(

[Across])

Where
Across: It is Optional. When we mention Across as ‘True’ that means it will merge cells in each row of the specified range as separate merged cells. The default value is False.

VBA Merge Range – Example1

Here is the example to merge multiple cells using ‘Range.Merge’ method of range object in worksheet when across is false.

Sub Range_Merge_Across_False()
Range("A1:D10").Merge
End Sub

The above macro or code will show you how to merge range(“A1:D10”) or multiple cells using VBA. In this example, we are merging range “A1 to D10” using ‘Range.Merge’ method of range object. Now the range(“A1:D10”) result will be a single cell named ‘A1’. In the above example across value is False. Please find the below screenshot for the same after merging the cells which is highlighted in yellow color.

Excel VBA Range Merge_False

VBA Merge Range – Example2

Here is the example to merge multiple cells using ‘Range.Merge’ method of range object in worksheet when across is true.

Sub Range_Merge_Across_True()
Range("A1:D10").Merge(True)
End Sub

The above macro or code will show you how to merge range(“A1:D10”) or multiple cells using VBA. In this example, we are merging range “A1 to D10” using ‘Range.Merge’ method of range object. Now the range(“A1:D10”) will be ten cells from ‘A1:A10’ instead of ‘A1:D10’. In the above example across value is True.Please find the below screenshot for the same after merging the cells which is highlighted in yellow color.

Excel VBA Range Merge_True

VBA Merge Rows- Example Macro

Here is the example vba code to merge multiple rows using ‘Range.Merge’ method of range object in worksheet.

Sub sbMergeRows()
Range("5:10").Merge
End Sub

This code will merge entire rows from row 5 to row 10.

VBA Merge Column – Example Macro

Here is the example vba code to merge multiple coluns using ‘Range.Merge’ method of range object in worksheet.

Sub sbMergeColumns()
Range("B:E").Merge
End Sub

This code will merge entire columns from column B to Column E.

Please note: Do not test sbMergeColumns code with the above example (sbMergeRows). As some of the cells are common in both and it will merge the maximum possible range. Test this examples on individual worksheets to see the output.

LIMITED TIME OFFER

3 Comments

  1. Younis January 20, 2016 at 12:53 PM - Reply

    Sir if we want to merge columns, like that A1:B1 and C1:D1 and E1:F1 and G1:H1.

    • PNRao January 21, 2016 at 9:25 PM - Reply

      Hi Younis,
      I have updated the article, please check the last two examples.
      Thanks-PNRao!

  2. di April 20, 2016 at 11:46 AM - Reply

    Hi I want to merge the rows column wise. Please suggest

Leave A Comment


Related pages


software project timeline templatevba drop downexcel vba select cellvba pastespecialstudy schedule template excelexcel 2007 vbacomplex macros in excelexcel vba instr functiondesign mode excelproject timeline excel templateexcel data entry form template excel 2007unlocking excel fileusing the macro recorder record a macroshortcut key for pivot table in excel 2007msgbox in vb6excel vba clearcontentsvba file nameexcel kpi dashboard templateexcel delete duplicate cellshow do you delete extra pages in excelexcel merge worksheets into oneclose database vbaapplication vbaadodb connection vba exceldcl databasedml commands in sql with syntax and examplesconditional statement in excelword 2007 add developer tab to ribbonuses of macro in excelgantt chart in excel templatevb adodb.connectionhow to unprotect a workbookjob estimate template wordmsgbox in vbaproject planning gantt chart excelworksheet or workbookdelete duplicate values in excelsql query interview questions and answers for experienced pdfexcel vba concatenateexcel vba loopsbasic excel macrosascending order worksheetexcel vba cell offsethow to use the vlookup function in excelmsgbox promptexcel append data from multiple sheetsvbscript msgbox exampleproject management excel spreadsheetexcel vba msgbox custom buttonsformula of vlookupremoving blank rows in excelvba commandsexport recordset to excelwhat is excel ribbonvba sumifs examplevba combobox valueactive worksheet vbahow do you insert a column in excelexcel vba delete entire rowschedule template for excelvba file existsexcel spreadsheet project plan templateexcel combine multiple worksheetsvba loopingshortcut key for excelhide duplicates excelvba userformsxlupvba redimvba msgbox ok cancelvba istextautofilter in vbacreating dynamic charts in excelimport text file to excel vbamacro remove blank rowsautofit column width