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


dml queries in sqlvlookup error refexcel macro to merge cellsvlookup formula examplehow to use vlookup function in excel 2010sql mcq questions with answers pdfexcel vba division remainderexcel developer ribbonexcel vba activeworkbook.saveasexcel advanced filterexcel combo box in cellvlookup error refsystem date in vbavariant data type in vbexcel if cell contains numberformatting graphs in excelvlookup for dummies 2010applying filters in excelexample of excel worksheetexcel vba recordset getrowsunhide all in excelfor each in excel vbaexcel vlookup function examplesvba excel isnumberhow to learn macro in excel 2007excel macro pasteadding password to excelopen a workbook in excel vbavba open file for inputcopy file vbahow to merge cells with data in excelexcel pie chart examplesautofit in excel 2007open excel file from access vbamacro enabled workbookexcel vba chartlook up functions in excelvba excel import text filems excel short keysaccess vba messageboxsave vbavba excel textboxvb6 connect to sql serverarrange ascending order in excelautorun macrohow to unhide multiple rows in excelexcel vba tips and tricksvba concatenate cellsadd drop down in excel 2007xlnone vbaexcel combo boxescreate macros excelhow to do a pivot table in excel 2010vb adodb.connectionhow to unlock excel workbookdelete all duplicates in excelmanaging multiple projects excel templatehow to save macros in excelvisual basic if statement multiple conditionshow to create a 3d pie chart in excel 2010excel worksheet name in celltemplate dashboard excelwhat is the concatenate function in excelexcel mergecellsweekly status report template wordinstr function vbafind duplicates in excel 2013sql aptitude questions and answers pdfaptitude and technical test questions and answersimport worksheet excelhow to check duplicate records in excelexcel button hyperlinkunprotect a workbook without passwordexcel hyperlink macroexcel chart title from cellwhat is a vba macrovba screenupdatingexcel sql vba